Introduction To Composite Key In SQL
To understand what a composite key in SQL, we must first understand what a primary key is. A primary key is a column in a SQL table that has a unique and non-null value. A composite key in SQL is similar to a primary key, except that it is created by combining more than one column to identify a certain row in the table.
SQL stands for Structured Query Language (pronounced “ess-cue-el”). SQL is a language for dealing with databases. It is the standard language for relational database management systems, according to ANSI (American National Standards Institute). SQL statements are used to conduct operations like updating data in a database and finding information from one. Oracle, Sybase, Microsoft SQL Server, Access, Ingres, and other relational database management systems that use SQL are just a few examples.
What is SQL?
The SQL language is the industry standard for dealing with Relational Databases. Inserting, searching, updating, and deleting database records are all possible with SQL. SQL may do a variety of different tasks, including database optimization and maintenance. SQL stands for Structured Query Language, which is pronounced “S-Q-L” or “See-Quel” in some cases… ANSI SQL is used by relational databases such as MySQL Database, Oracle, MS SQL Server, Sybase, and others.
Here are 5 types of SQL queries that are widely used:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Data Query Language (DQL)
Uses:
Here are several compelling reasons to use SQL.
- It enables users to access data stored in a relational database management system (RDBMS).
- It aids in the description of data.
- It enables you to define data in a database and manipulate that data.
- SQL allows you to create and delete databases and tables.
- SQL allows you to build a view, a stored procedure, and a function in a database.
- Permissions can be configured on tables, processes, and views.
Composite Key in SQL
A composite key in SQL is a collection of columns that are used to uniquely identify all of the rows included. Despite the fact that a single column cannot uniquely identify any row, a combination of more than one column can uniquely identify any record. To put it another way, the combination key is a primary key that is generated by combining many columns. Different columns’ data types, on the other hand, could be different. In SQL, you can also build a composite key in SQL by combining all the foreign keys.
A composite key in SQL is created by combining two or more columns in a table to uniquely identify each row in the table. When the columns are combined, the uniqueness of a row is guaranteed; however, when the columns are taken individually, uniqueness is not guaranteed. It can also be thought of as a primary key created by combining two or more attributes to uniquely identify every row in a table. If you are still confused and want to have another definition of the definition composite then you can click here.
Here are several compelling reasons to use SQL.
- It enables users to access data stored in a relational database management system (RDBMS).
- It aids in the description of data.
- It enables you to define data in a database and manipulate that data.
- SQL allows you to create and delete databases and tables.
- SQL allows you to build a view, a stored procedure, and a function in a database.
- Permissions can be configured on tables, processes, and views.
Example: For a better understanding, consider using an example to illustrate this topic. Assume you’re in charge of a company’s personnel data and you’re looking for an employee called Rahul in your database. However, when searching by name, there’s a good chance that more than one employee has the same name.
This also happened in this case. You came across a number of employees with the same name, Rahul. Now, because you already know that employee numbers are always unique, you may improve your search by treating the name column and the employee number column as a single column. This is an example of how Composite key in SQL can be used.
2nd Example: For example, suppose we wish to keep track of a student’s class registration and grade history. We’ll need to keep track of which students took which classes during which quarters, as well as the dates of enrollment, drop (if the class was dropped), and final grade. The following is how our table will look:
- Table Class_History
- Quarter_ID
- Student_ID
- Course_ID
- Date_Registered
- Date_Dropped
- Final_Grade
The first three fields in this table, Quarter_ID, Student_ID, and Course_ID, create a composite key in SQL. Each of them cannot uniquely identify each record on its own, but when all three are used together, each record can be uniquely identified.
SQL Syntax to specify composite key in SQL:
CREATE TABLE TABLE_NAME
(COLUMN_1, DATA_TYPE_1,
COLUMN_2, DATA_TYPE_2,
???
PRIMARY KEY (COLUMN_1, COLUMN_2, …));
In all cases, the composite key in SQL created consists of COLUMN1 and COLUMN2. Now let’s see the syntax used on different platforms.
MySQL:
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));
Oracle:
CREATE TABLE SAMPLE_TABLE
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));
SQL Server:
Let’s see the Syntax for the select top statement:
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));
Consider the STUDENT table as an example of how to understand the idea of composite keys in SQL.
Table STUDENT:
|
S_Name |
S_Class |
Parent_Contact_No |
S_Age |
|
Mehul |
6 |
8700867330 |
11 |
|
Rashi |
5 |
8700867330 |
10 |
|
Mehul |
6 |
9990155289 |
11 |
|
Vansh |
7 |
9354226009 |
12 |
|
Ishita |
9 |
8681012213 |
14 |
You’ll notice
that there are no unique values in any of the columns in the table above. However, you can fulfill the objective of creating a unique column by merging the columns S_Name and Parent_Contact_No.
To generate the above table and declare a composite key in SQL utilizing the columns of that table, SQL queries were used.
Create a table named STUDENT
Create table STUDENT
(declaring columns with different data types)
S_Name varchar(30),
S_Class integer,
Parent_Contact_No integer,
S_Age integer,
(declare the composite key in SQL)
Columns: S_Name and Parent_Contact_No are combined to create the composite key in SQL both columns have different data types:
CONSTRAINT My_Composite_Key PRIMARY KEY (S_Name, Parent_Contact_No)
);
Add values to the table
Insert into STUDENT values(‘Mehul’, 6, 8700867330, 11);
Insert into STUDENT values(‘Rashi’, 5, 8700867330, 10);
Insert into STUDENT values(‘Mehul’, 6, 9990155289, 11);
Insert into STUDENT values(‘Vansh’, 7, 9354226009, 12);
Insert into STUDENT values(‘Ishita’, 9, 8681012213, 14);
(select all values of the table to display the content)
Select * from STUDENT;
Composite Key in SQL Declaration
When more than one column or field in a table is combined to accomplish the goal of uniquely identifying row values, the result is a composite key in SQL, which can be either the table’s main or candidate key.
The SQL Syntax for the COMPOSITE KEY Constraint is as follows:-
Code:
Create table table_name
(Col1 data_type_1 NOT NULL,
Col2 data_type_2 NOT NULL,
Col3 data_type_3,
Col4 data_type_4,
…
CONSTRAINT COMP_NAME PRIMARY KEY (Col1, Col2)
(Composite key in SQL declaration)
);
Example:
Create table Personal
(
Person_ID INT NOT NULL,
Person_FNAME VARCHAR(25),
Person_LNAME VARCHAR(25),
Person_PHONE INT,
Person_ADDRESS VARCHAR(25),
CONSTRAINT COMP_KEY PRIMARY KEY (Person_ID, Person_PHONE)
);
(Composite Key in SQL Declaration)
In the example above, we’re establishing a table called “stud” in which the composite keys “S FNAME” and “S LNAME” are joined. A composite key in SQL will be one-of-a-kind and will not be NULL.
In SQL, below are some examples of composite keys.
Let’s look at a few examples to help us learn composite keys:
Example 1: Consider Tables Buyer and Item:
Table BUYER:
|
B_ID (Primary Key) |
B_Name |
B_Contact_No |
B_Address |
|
101 |
Mohan |
8700867330 |
Delhi |
|
102 |
Pankaj |
8681012213 |
Noida |
|
103 |
Diya |
8090100224 |
Saket |
|
104 |
Rohit |
9354226009 |
Gurugram |
|
105 |
Gaurav |
9990155289 |
Noida |
As you can see, the column B ID is the main key of the table BUYER since it uniquely identifies all table records. This column will also serve as a foreign key for another table called ITEM.
- SQL queries for the table BUYER.
Create table BUYER
(
(declaring columns of the table)
B_ID int NOT NULL,
B_Name varchar (30),
B_Contact_No int,
B_Address varchar (50),
—- declare the primary key —-
PRIMARY KEY (B_ID)
);
- Add values to the table
Insert into BUYER values(101, ‘Mohan’, 8700867330, ‘Delhi’);
Insert into BUYER values(102, ‘Pankaj’, 8681012213, ‘Noida’);
Insert into BUYER values(103, ‘Diya’, 8090100224, ‘Saket’);
Insert into BUYER values(104, ‘Rohit’, 8090100224, ‘Gurugram’);
Insert into BUYER values(105, ‘Gaurav’, 9990155289, ‘Noida’);
Select all values of the table to display the content
Select * from BUYER
Table ITEM:
|
B_ID (Foreign Key) |
Order_No |
I_ID |
I_Name |
I_Quantity |
|
101 |
1 |
201 |
Laptop |
1 |
|
102 |
2 |
205 |
Headphones |
2 |
|
103 |
3 |
202 |
Mobile |
2 |
|
102 |
2 |
202 |
Keyboard |
1 |
|
101 |
1 |
204 |
Speaker |
4 |
The composite key in SQL is made up of B ID, Order No, I ID.
The column B_ID acts as a foreign key in the table ITEM, and there is no column that can act as the primary key. This means there isn’t a field that can be employed to uniquely identify all of the table records. To establish a primary key, combine the columns B_ID, Order_No, and I_ID to form a composite key in SQL. These three columns can be used to form a primary key for the table ITEM. 3
SQL queries for the table ITEM.
create a table named ITEM
Create table ITEM
(declaring columns of the table)
(
B_ID int NOT NULL,
Order_No int,
I_ID int(10),
I_Name varchar(20),
I_Quantity int,
(Declare the composite key in SQL)
CONSTRAINT MyCompositeKey PRIMARY KEY (B_ID, Order_No, I_ID)
);
add values to the table —-
Insert into ITEM values(101, 1, 201, ‘Laptop’, 1);
Insert into ITEM values(102, 2, 205, ‘HeadPhones’, 2);
Insert into ITEM values(103, 3, 202, ‘Mobile’, 2);
Insert into ITEM values(102, 2, 202, ‘Keyboard’, 1);
Insert into ITEM values(101, 1, 204, ‘Speaker’, 4);
(select all values of the table to display the content)
Select * from ITEM
As you can see, composite key in SQL are used to uniquely identify all of the rows that are involved. When you need keys that can uniquely identify records for better search purposes but don’t have a single unique column, composite key in SQL come in handy. To build a unique key in such circumstances, you must mix many columns.
For a better understanding, consider using an example to illustrate this topic. Assume you’re in charge of a company’s personnel data and you’re looking for an employee called Rahul in your database. However, when searching by name, there’s a good chance that more than one employee has the same name. This also happened in this case. You came across a number of employees with the same name, Rahul. Now, because you already know that employee numbers are always unique, you may improve your search by treating the name column and the employee number column as a single column. This is an example of how Composite key in SQL can be used.
Advantages Of Composite Key in SQL
Because composite key in SQL are generally made up of many natural key qualities, they provide advantages similar to natural keys.
- Storage:
Composite key in SQL consume less disc space than defining a surrogate key column because the composite key in SQL already exists as a character in the database and does not need to be defined in the table, particularly for the purpose of unique ide
ntification. This makes the table more streamlined while also saving space.
- It’s a lot less complicated to set up and use:
Composite key in SQL are simple to implement in a database schema since the components are already named entries in the database. When they are also natural keys, they are frequently intuitive for real-world scenarios. Non-composite keys are frequently used when a composite key in SQL does not always uniquely identify a record. A personal name, for example, may or may not be unique in a database, and some other field, such as date of birth, may be included to raise the possibility of uniqueness.
Disadvantages Of Composite Key in SQL
- Prerequisites Changes
The format of certain real-world entities can vary as a result of changing business requirements and laws. Composite key in SQL are made up of many natural keys that are tied to the actual world, and like their format in the real world changes, so does their format in the database. This is inconvenient since the amount of composite key in SQL characteristics will vary, requiring all foreign keys to be changed.
- Storage and Complexity
A composite key in SQL is made up of numerous attributes, and it will be used as the foreign key in multiple tables. This consumes a lot of disc space since multiple columns are kept as the foreign key instead of just one. This complicates the design and increases the CPU cost of queries because each join requires the DBMS to compare three attributes instead of only one in the case of a single natural key.
What is NoSQL?
A new sort of database management system is known as “NoSQL.” The fact that it does not follow Relational Database Concepts is its greatest differentiating factor. The acronym NoSQL stands for “Not Only SQL.” As internet behemoths like Google, Facebook, and Amazon dealt with large amounts of data, the concept of NoSQL databases grew in popularity.
When a relational database is used to hold massive amounts of data, the system becomes slow to respond. To solve this, we might “scale up” our systems by modifying our existing hardware. As our database demand grows, we may need to distribute it across multiple hosts to avoid the problem described above. “Scaling out” is the term for this.
Non-relational databases that scale-out better than relational databases and are created with web applications in mind are known as NoSQL databases. They don’t query the data with SQL, and they don’t follow rigorous schemas like relational models. The ACID (Atomicity, Consistency, Isolation, and Durability) properties of NoSQL are not always ensured.
How to Drop and alter Composite Keys in SQL?
Composite keys, like any other column in a table, can be changed using the edit commands. Using the change command with the composite key in SQL, you can simply do add and drop actions in a table. The alter-add command can be used to add more columns to the set of columns that make up the composite key. You may also use the alter-drop command to remove a column from the group of columns that make up the composite key.
- ALTER-ADD command: The ALTER-ADD command is used to add more columns to the composite key’s existing set of columns.
Syntax: Syntax for adding columns to a composite key that already exists
ALTER table table_name
ADD CONSTRAINT Constraint_name PRIMARY KEY (COL1, COL2, COLN);
Example:
ALTER table STUDENT
ADD CONSTRAINT MyComp_Scores PRIMARY KEY ( Scores, Percentage );
This will add scores and percentage columns to the table’s composite key.
- ALTER-DROP command: The ALTER-DROP command is used to remove a column from the composite key’s existing set of columns.
Syntax: Syntax for removing a column from a composite key that already exists.
ALTER table table_name
DROP CONSTRAINT Constraint_Name;
Example:
ALTER table STUDENT
DROP CONSTRAINT MyComp_Scores;
(This will remove the constraint from the table’s composite key, MyComp Scores.)
In MySQL, SQL Server, and Postgresql, how do you make composite key in SQL?
MySQL
Syntax
(Syntax to create a composite key in SQL for a table by combining some columns)
Create table table_name (
COL1 data_type_1,
COL2 data_type_2,
COL3 data_type_3,
COLN data_type_n,
(Declare the composite key)
Here COL1 and COL3 are forming up the composite key in SQL
PRIMARY KEY (COL1, COL)
);
Example:
Create table STUDENT
(declaring columns with different data types)
(
S_ID integer,
S_Class integer,
S_Age integer,
S_Phone integer,
(declare the primary key)
PRIMARY KEY (S_ID, S_Phone)
);
SQL Server
Syntax
Syntax to create a composite key in SQL for a table by combining some columns
Create table table_name (
COL1 data_type_1,
COL2 data_type_2,
COL3 data_type_3,
COLN data_type_n,
(Declare the composite key in SQL here COL1 and COL3 are forming up the composite key in SQL)
PRIMARY KEY (COL1, COL)
);
Example:
Create table STUDENT
(
Declaring columns with different data types
S_ID integer,
S_Class integer,
S_Age integer,
S_Phone integer,
(Declare the primary key)
PRIMARY KEY (S_ID, S_Phone)
);
Postgresql
Syntax
(Syntax to create a composite key in SQL for a table by combining some columns)
Create table table_name (
COL1 data_type_1,
COL2 data_type_2,
COL3 data_type_3,
COLN data_type_n,
(Declare the composite key in SQL here COL1 and COL3 are forming up the composite key in SQL)
PRIMARY KEY (COL1, COL)
);
Example:
Create table STUDENT
(
(Declaring columns with different data types)
S_ID integer,
S_Class integer,
S_Age integer,
S_Phone integer,
(Declare the primary key)
PRIMARY KEY (S_ID, S_Phone)
);
Conclusion
You learned about the composite key in SQL in this blog of composite key in SQL. This blog covered the fundamentals of composite key SQL and how to use them. You explored primary and composite keys in-depth, as well as their applications and examples. It also included examples of composite key creation in MySQL, SQL Server, and Postgresql. Please do not hesitate to contact us if you have any questions.
Frequently Asked Question’s
1. What is a composite key with example?
Our primary key in a table of students would now be firstName + lastName. These properties are not simple keys because students can have the same firstnames or lastnames. For students, the primary key firstName + lastName is a composite key.
For a primary-key or foreign-key constraint, a composite key specifies many columns. The following example makes two tables. A composite key acts as a primary key in the first table, while a composite key acts as a foreign key in the second table.
3. What is the difference between the primary key and composite key?
While a primary key and a composite key may perform the same functions, the primary key will only have one column, whereas the composite key will have two or more. The main key and a foreign key have quite different relationships. The important point to remember is that the primary key in one database table becomes a foreign key in another, and vice versa.
A foreign key in a database table is a column that is copied from another table and used to link database entries to that external table. The primary key of the other table is the foreign key in the database table where it sits.
There is no evidence that composite primary keys are inherently problematic. The optimum approach is to have a column (or several columns) that uniquely identifies each row. In some tables, however, a single column is insufficient to uniquely identify a row. A composite primary key is possible in SQL (and the relational paradigm).
5. Can composite key be foreign key?
A foreign key with two or more columns is known as a composite foreign key. It’s worth noting that every column in a single foreign key must refer to the same table. In other words, a foreign key that refers to a column in Table 1 and a column in Table 2 is not possible.
6. Can a primary key be a composite key?
Primary keys must have distinct values. NULL values are not allowed in a primary key column. A table can only have one primary key, which can be made up of one or more fields.
Composite keys are utilized when numerous fields are used as the main key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value in that field(s).
Hi, Null values are not allowed in composite primary key columns. Each main key column would be verified to ensure that null values are not passed on. If you’ve specified a Unique constraint, there’s a risk that NULL values will be accepted.
A composite unique key is a key that is made up of several columns. Because Oracle constructs an index on a unique key’s columns, a composite unique key can have up to 16 columns. You must use table_constraint syntax rather than column column_constraint syntax to define a composite unique key.
No two rows in the database can have the same mix of values in the key fields to meet a constraint that specifies a composite unique key. The constraint is immediately satisfied by any record with nulls in all key columns. Two rows with nulls in one or more key columns and the identical mix of values in the other key columns, on the other hand, violate the requirement.
9. How do you create a composite key in access?
Hold CTRL and click the row selector for each field to pick multiple fields to create a composite key. Click Primary Key in the Tools group on the Design tab. To the left of the field or fields, you specify as the primary key, a key indicator is added.
10. What is the composite key in hibernate?
A Composite Main Key is a primary key that is made up of one or more columns. A composite primary key or composite key is a database table that contains more than one primary key column.
