SQL Composite Key

SQL Composite Key

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

Syntax(Multiple Datatypes):

CREATE TABLE Compkey
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));

Table Created;

insert into compkey values(1,'parthi','cse');
insert into compkey values(2,'karthi','cse');
insert into compkey values(1,'karthi','cse');

OutPut

Select * From comkey

COL1 COL2 COL3
1          parthi cse
2          karthi cse
1           karthi  cse

Note: Both are different and unique Its not duplicate a duplicate of 1 

Syntax(Same Datatypes) 

CREATE TABLE compkey1
COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL2, COL3));

Here Col2 and Col3 set as a primary key

insert into compkey1 values(1,'par','cse');

insert into compkey1 values(1,'cse','parthi');

insert into compkey1 values(1,'ece','cse');

insert into compkey1 values(1,'mech',null);

Select * from comkey1

COL1 COL2 COL3

1          par            cse

1          cse           par
1           ece            cse

Note : when inserting null values it will not accepted

Interview Questions:

1.How many primary key in one table?

Answer: only one primary key.

2.If i need two colomn unique record if there is any possible to create multiple primary key.

Answer: Yes by using composite key to create more than one primary key in table.

3.After composite primary key is set If Duplicate values and null values are added into the table what will happen?

Answer : It never accept the null values and duplicate values Its only support unique record.