Composite Key In SQL
What is the Composite Key in SQL ?
To know exactly what a composite key is, we first need to know keys in DBMS. As we know that relational databases consist of relations or tables, which store the data in the form of rows and columns, hence, the key plays a very crucial role in relational databases, it is used for identifying unique rows in the tables and it also establishes the relationship between different tables in a database.
For example, the Roll number of any student can be considered as a key, because it is unique for each student. There are many different types of keys in DBMS.
Let's see a bit about each of them :
Primary Key
Primary key is a column or a group of columns that are used to uniquely identify the rows in a table. Primary keys are always unique and they can't be NULL.
Example of primary key :
The roll number can be considered as the primary key for the student details table, as it uniquely identifies the data of each student.
Candidate Key
Candidate key is also not null and a unique key that helps us uniquely identify rows in a table. You might be wondering if it is similar to the primary key, but that's not the case because as we know that table can only contain one primary key, so all the remaining columns or a group of columns that are eligible to be a primary key are referred to as the candidate keys. The name "candidate" is itself speaking that they are participating as the candidate.
Example of candidate key :
As we know that the roll number is the primary key for the student details table, but all the other columns like registration number, aadhar number are considered to be the candidate keys as they are also unique and not null.
Super Key
Super key is the set of all those keys that uniquely identify a row. As the name suggests, it is superior to all, and all the candidate keys are the subsets of the super key, which means that all the candidate keys are the super key. But the reverse of this is not true. Also, a table can have multiple super keys.
Example of super key :
In the student details table, the super key consists of the roll number, aadhar number, registration number, roll number + name of the student (although the name of the student can't be unique its combination with roll number is always unique as the roll numbers are unique for each student). These are all super keys.
Foreign Key
A foreign key is a column (or collection of columns) in a table, that refers to the primary key in another table. The table with the foreign key is known as the child table, and the table has the primary key is known as the parent table. The Foreign key is used to establish the relationship between the tables in the database.
Example of foreign key :
Let's suppose we have a student details table which is storing the roll number, aadhar number, and the registration id of the student, here the roll number field is considered as the primary key as it is unique to each student.
Now, suppose there is another table named the registration details, and this table is storing the details of the student registrations like registration id, registration date, and session, now for this registration details table, the registration id is considered as the primary key.
The registration in the student details table is the foreign key as it is pointing to the primary key (registration id) of the registration details table.
Alternate Key
In simple words, we can understand that the table can have many candidate keys but a single primary key, so all other remaining keys in the candidate keys excluding the primary key are the alternate key. Alternate keys can be NULL and there can be multiple alternate keys in a table as we know that there are multiple candidate keys in a table out of which only one key is selected as the primary key, so all the remaining keys are the alternate keys.
Alternate key = candidate key - primary key
Example of alternate key :
As we discussed above, in the student details table, roll number, registration number, and aadhar number is considered to be the candidate keys, but roll number is the only primary key, so the others (registration number, and aadhar number) are the alternate keys.
Composite Key
Composite key is a combination of two or more columns in a table that uniquely identify each row in the table. When these columns are combined, then the uniqueness of a row is guaranteed, but when the columns are taken individually, then it does not guarantee the uniqueness.
It can also be called a primary key made by the combination of two or more columns to uniquely identify every record in a table.
Note :
Primary key, super key, candidate key, or any other key can be called as the composite key if it consists of two or more columns to uniquely identify the records in a table.
Example of composite key :
Let's suppose that in the student details table, there are two fields namely the first name and the last name of the student, the first name and last name fields individually are not the simple keys because the student can have the same first name or the same last name, but the combination of first name + last name is unique, so we can consider that the composite primary key in this table is first name + last name.
We can analyze this situation from the below diagram.
As we can see from the above table, we have duplicate entries for the first name as well as for the last name, but the combination of both fields is unique.
Note :
Composite key cannot be null and should be unique.
In this article, we will deep dive into composite keys, how we can use composite keys, what is the need for composite keys, and various examples to understand the concept.
Note :
A table can have only one primary key, which may consist of single or multiple attributes or columns. When multiple columns are used as a primary key, then they are called a composite key.
How to Use Composite Keys in SQL ?
Before going on to how to use composite keys, let's see how to use primary keys. We can declare the primary key while creating a table, let's see the syntax for that.
Or we can mention the column as the primary key at the time of definition only.
Similar to this we can also use the syntax of COMPOSITE KEY by making small changes in the syntax of creating PRIMARY KEY.
Note :
The columns that are used for creating composite keys can be of different data types.
In this, we made the composite key the combination of two columns i.e. column 1 and column 2 because all the rows of the table student can be uniquely identified by this composite key.
When does the Composite Key Come into the Picture ?
It is considered the best practice to select a minimum number of attributes (or columns) to uniquely identify each record in a table, but in a situation where this is not possible, as a single column is not able to serve the purpose of uniquely identifying the record, then composite key comes into the picture, it states that no problem if any column is not the primary key, we can make the combination of two or more columns to uniquely identify the records, and the combination of these columns should serve the purpose of the primary key. Composite keys are also called concatenation keys or compound keys.
For example, as we have seen above if we have a table having two fields namely the first name and the last name of the student :
| First name | Last name |
|---|---|
| Shipri | Meena |
| Yamini | Gautam |
| Shipri | Agrawal |
| Heena | Agrawal |
Then the first name and the last name fields individually can not be the simple primary keys as the students have the same first name or the same last name. So, we can't build a primary key here using any of the single fields, in this situation the use of composite key comes into the picture as we can see the combination of first name and last name is unique for this table, so we can consider that the composite primary key is the combination of first name and last name here.
Composite Primary Key = First Name + Last Name
To conclude, we can say :
In a table sometimes, we do not have any unique column, which could be defined as a primary key. In such a case, we combine two or more columns from a table, make it unique, and use it as the primary key in a table. These combined columns or attributes are called Composite keys.
How to Alter and Drop Composite Key in SQL ?
You can drop a composite key in a table, by making use of the DROP keyword. The DROP CONSTRAINT command is used to remove the existing key from the table, its syntax is.
We can understand this with the same example we took above :
OR
If you want to alter the composite key from the existing table, then you can use this syntax :
we can understand this with the same example we took above :
Here, composite_key , is the name of the composite key we are creating.
Examples of Composite Key in SQL :
Case - 1 : Creating a composite key.
Let's create a table having name student_details and the fields as First_name, Last_name and Parents_phone_number :
Note :
Before creating a composite key, we should know that the columns which we are using to create the key, and their combination should not be null and should not have duplicate values. and they should be able to uniquely identify the records in a table.
In this example, we have created a composite key as the combination of two columns i.e. First name, and parent's phone number because all the rows of the table student can be uniquely identified by this composite key.
Now, let's insert some data in the table :
Let's take a look at the table :
Output :
| First_name | Last_name | Parents_phone_number |
|---|---|---|
| Sonia | Gandhi | 6293982832 |
| Priya | Pandey | 8377424677 |
| Riya | Gandhi | 6293982832 |
| Sonia | Sharma | 9828532461 |
Here, as we can see from the output table, no single attribute (column) uniquely identifies the records in a table, but the combination of first name and the parent's phone number can identify the records in a table uniquely.
Hence, composite primary key = First_name + Parents_phone_number
Case - 2 : Deleting the existing composite key from the table. For dropping the already existing composite key, we query this;
Let's try this out in our student_details table example :
Conclusion
- Keys are used in relational databases to uniquely identify the records and to establish a relationship between different database tables.
- There are different types of keys like super key, candidate key, primary key, alternate key, composite key, foreign key etc.
- Primary key is a key that is used to uniquely identify the rows in a table. Each table can have only ONE primary key and it should be unique and NOT NULL.
- A Foreign Key is a key whose values match a Primary Key in a different table. It is used to link and establish a relation between different database tables.
- Super key is the set of all those keys that uniquely identify a row.
- A candidate key is the minimal set of columns that can uniquely identify a row in a table, there can be more than one candidate key in a table and they should be unique and NOT NULL.
- Alternate keys are those candidate keys that are not the Primary key. They are all the remaining keys other than the primary key. They are also known as the secondary keys.
- Primary key, super key, candidate key, or any other key can be called as the composite key if it consists of two or more columns to uniquely identify the records in a table.
- Composite key is the combination of two or more columns used for uniquely identifying each record in a table.
- Composite keys are unique and not null.
- The main difference between the primary key and the composite key is the primary key is derived from a unique column. Composite is derived by a combination of two or columns. Individually they are not unique but when combined they provide uniqueness.
- After getting the composite key, we mention it as the primary key Which will be used for the identification of the rows from the table.
- If a table already consists of a primary key having a single attribute as an entity for uniquely identifying the records in a table, then the composite key is of no use as we always select the primary key having minimum attributes (or columns).