Composite key in SQL

Are you ever surprised looking at duplicate values in column of a database table which has a primary key constraint? I did. I have intermediate-level knowledge of the RDBMS and SQL. I never worked before with a big database that has huge number of tables and records.

While working on the project, I found one column in the table has multiple duplicate values in it and same column having the primary key constraint. Till then I only heard or read that the primary key does not have duplicate values in the column. After searching on the internet,  I learned that we can use a combination of two or more primary key column as one primary key.

A primary key that is made by a combination of two or more columns is known as a composite key.

The syntax for creating the composite key is

CREATE TABLE TABLE_NAME (
    column1 int,
    column2 int,
    column3 varchar(250),
    PRIMARY KEY (column1, column2)
)

In the above example tenantID and idcatgory are the primary keys of the table. You can see that tenantID has duplicate values but the combination of two columns is unique.

Hope you learned something new about SQL and primary key.

 

 

 

Add comment