How To Define A Composite Key In SQL? Explanation With Example
Summary: You will learn how to define a Composite Key In SQL? let’s also understand their importance with examples in this article.
What Is Composite Key In SQL
A composite key in SQL is made by the amalgamation or combination of two or more columns in a table that can be used uniquely to recognize each row in the table when the columns are combined uniqueness is guaranteed when we took it separately it doesn’t guarantee uniqueness.
In other words, we can say that a composite key in SQL is derived from a combination of two or more columns that combined make a unique column, which individually doesn’t provide uniqueness. we are able to use all foreign keys within the composite keys. Furthermore, the Data types specified in the composite key can be different.
But sometimes more than one attribute is needed to uniquely identify an entity. A primary key in SQL Server which is made by the combination of more than one attribute is known as a composite key.
It is obtained in the form of the below pattern:
Note: A composite key also can be created by the combination of more than one candidate key.
The composite key in SQL can’t be null.
Example:
First of all, let’s Create a database:
|
1 |
CREATE College; |
Here, Using database:
|
1 |
USE College; |
After that, Creating a table with a composite key in SQL:
|
1 2 3 4 5 6 7 |
CREATE TABLE College (Stu_RollNumber INT, Name VARCHAR(30), Class VARCHAR(30), Section VARCHAR(10), Email VARCHAR(30), PRIMARY KEY (Stu_RollNumber, Email)); |
Note: In this illustration, we have made the composite key as the combination of two columns i.e. Stu_RollNumber and Email because all the rows of the table student can be uniquely identified by this composite key.
Furthermore, we Inserted records in the table:
|
1 2 3 4 5 6 7 8 |
INSERT INTO College (Stu_RollNumber, Name, Class, Section, Email) VALUES (11, 'ADITYA SHUKLA','SIX', 'A', 'SULTANPURHOME@GMAIL.COM'); INSERT INTO College (Stu_RollNumber, Name, Class, Section, Email) VALUES (12, 'PRATIMA SHUKLA','NINE', 'C', 'PRATIMASHUKLA04@GMAIL.COM'); INSERT INTO College (Stu_RollNumber, Name, Class, Section, Email) VALUES (13, 'ANKIT SINGH','TENTH', 'B', 'ANKITSHUKLA09@GMAIL.COM'); INSERT INTO College (Stu_RollNumber, Name, Class, Section, Email) VALUES (14, 'PINKI GUPTA','EIGHT', 'A', 'PINKIGUPTALKO08@GMAIL.COM'); |
Let’s get the records from the table as follows Query:
|
1 |
SELECT * FROM College; |
When Does Composite Key in SQL Come to the Scenario?
sometimes in our table, we don’t have any unique column, which could be defined as a primary key. In that 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 attributes or columns are called Composite Key.
Syntax:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Table_Name ( Col1 DATA_TYPE_1, Col1 DATA_TYPE_2, Col1 DATA_TYPE_3, Col1 DATA_TYPE_4, ColN DATA_TYPE_N, CONSTRAINT COMP_NAME PRIMARY KEY (Col1, Col2) ---Composite key declaration--- ); |
Let’s Know How To Use Composite Key in SQL?
Before going with the usage yield step by step by differentiating primary and composite keys.
The composite key differs from a primary key and it’s important to understand however the two vary, each in syntax and usage. One will say that combining a set of multiple columns in a table, results in a primary key, as it will have a unique value in every row.
I). Primary Key Declaration
The primary key is defined as it is a constraint that accepts or recognizes all of the columns in a row as unique. Therefore, for a constraint to be recognized as a primary key, it should contain unique values throughout the row & none of the values should be NULL. In a table, there can only be one(1) primary key. Furthermore, a primary key can have one(1) or as many columns as possible. Syntax declaration in SQL for PRIMARY KEY Constraint defines as below:
SQL Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE Table_Name ( Col1 DATA_TYPE_1 NOT NULL, Col2 DATA_TYPE_2 NOT NULL, Col3 DATA_TYPE_3, Col4 DATA_TYPE_4, ColN DATA_TYPE_N, PRIMARY KEY (Col1) ---Primary key declaration--- ); (OR) CREATE TABLE Table_Name ( Col1 DATA_TYPE_1 PRIMARY KEY, --- Primary key declaration--- Col2 DATA_TYPE_2 NOT NULL, Col3 DATA_TYPE_3, Col4 DATA_TYPE_4, ColN DATA_TYPE_N, ); |
II). Composite Key Declaration
When over one column or field in a table are combined to realize the task of uniquely characteristic row values, then that composite key is either a primary or a candidate key of that table. SQL Syntax declaration for COMPOSITE KEY Constraint defines as given below code: –
SQL Code
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Table_Name ( Col1 DATA_TYPE_1 NOT NULL, Col2 DATA_TYPE_2 NOT NULL, Col3 DATA_TYPE_3, Col4 DATA_TYPE_4, ColN DATA_TYPE_N, CONSTRAINT COMP_NAME PRIMARY KEY (Col1, Col2) ---Composite key declaration--- ); |
Example: To Understand The Composite Key Declaration
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Student ( Student_ID INT NOT NULL, Student_FNAME VARCHAR (30), Student_LNAME VARCHAR (30), Student_BRANCH VARCHAR (30), Student_PHONE INT NOT NULL, Student_ADDRESS VARCHAR (25), CONSTRAINT COMP_KEY PRIMARY KEY (Student_ID, Student_PHONE) --- Composite key declaration--- ); |
Explanation: In the above example we can see that, we are creating a table called “Student”, in which “Student_ID” and “Student_PHONE” combined becomes a composite key. A composite key will be unique and NOT NULL.
Examples of Composite Key in SQL
Let us see and consider an example to understand the example of composite key in SQL.
Example:1
Step:1 First of all, Write a SQL query to create the Customer table
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Customers( Customer_ID INT NOT NULL, Customer_FNAME VARCHAR (25), Customer_LNAME VARCHAR (25), Customer_ADDRESS VARCHAR (25), City VARCHAR (25), Customer_PHONE INT, PRIMARY KEY (Customer_ID) ---Primary key declaration--- ); |
Customers Table:
Therefore, In the above table you can see that, we have “Customer_ID” which is unique and NOT NULL. Thus, This will act as a foreign key in the “Customers_Orders“Table.
Step:1 Write a SQL query to create the Customers_Orders table
|
1 2 3 4 5 6 7 |
Create table Customers_Orders ( Customer_ID INT NOT NULL, Order_ID INT NOT NULL, Product_ID INT NOT NULL, Quantity INT, CONSTRAINT COMP_K PRIMARY KEY (Customer_ID, Order_ID, Product_ID) --Composite key declaration-- ); |
Customers_Orders Table:
Explanation: Therefore, as you can see in the above “Customers_Orders” table, we have columns such as “Customer_ID”, “Order_ID”, “Product_ID” and “Quantity”. Here “Customer_ID” is a primary key in the table “Customers”. However, in the table “Customers_Orders”, it is duplicated and we cann’t make it as the primary key. Furthermore, It goes the same with the “Order_ID” and “Product_ID”.
Therefore, we have combined all the three(3) columns & make it to a composite key.
How To ALTER and DROP composite key in SQL?
After the declaration of the composite key, generally, you want to make changes in the columns specified in the composite key. this will be done by the “ALTER” command.
I). ALTER Composite Key
Suppose that, if you want to alter the composite key from the existing table. then we can use the given below syntax.
Syntax:
|
1 2 |
Alter table <table_name> ADD CONSTRAINT <Const_name> PRIMARY KEY (COL1_NAME, COL2_NAME, … , COLN_NAME); |
Example of ALTER Composite Key
|
1 2 |
Alter table Customers_Orders DROP CONSTRAINT (Customer_ID, Order_ID, Product_ID, <new_columnname>); |
II). DROP Composite Key
Furthermore, if you wish to drop the composite key from the existing table. then we can use the given below syntax.
Syntax:
|
1 2 |
Alter table <tab_name> DROP CONSTRAINT <CONST_NAME>; |
Example of DROP Composite Key
|
1 2 |
Alter table Customers_Orders DROP CONSTRAINT (Customer_ID, Order_ID, Product_ID); |
Important Points To Remember
Things that require to be kept in your mind for the composite key is mentioned in below points: –
Note: Composite key = two or more columns
Composite isn’t NULL & UNIQUE
Foreign key will be involved or included in the composite key
After identification of the composite key, we mention it as a primary key within the table. which is able to be used for the identification of the rows from the table.
Difference Between The Primary Key And The Composite Key
The primary key in SQL is derived by a column that is unique.
The Composite key is derived by a combination of two or more columns. Separately or Individually they aren’t unique but combined they provide uniqueness.
Conclusion:
You have learned in this article, Composite Key In SQL, Primary Key Declaration, Composite Key Declaration, How To ALTER and DROP composite key in SQL, and Difference Between The Primary Key And The Composite Key etc. I hope you will enjoy it!
About Author
