The difference between Primary keys and Composite key as given below.
A PRIMARY KEY constraint uniquely identifies each record in a table .
A Primary keys column must contain unique values and cannot have null values.
A table can have only one primary key, which may consist of single or multiple columns.
A COMPOSITE KEY is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.
Note that the columns that make up a composite key can be of different data types.
So basically, primary key becomes the composite key when more than column is used to uniquely identify each row in the table.
Creating a Primary key on table column
SYNTAX
create table table_name (
column1 datatype1,
column1 datatype1,
column1 datatype1,..
primary key (column_name)
Lets look at an example of primary key in SQL Server.
Following statement create a new table named as Customer with primary key on column custId
CREATE Table dbo.Customer (
CustId INT NOT NULL,
CustName VARCHAR(150) ,
CustCode VARCHAR(20),
CustMailId VARCHAR(30)
PRIMARY KEY (custId)
)
A primary key is specifiedon column CustId, it means CustId column can not have null values also duplicate values can not be allowed.
An error message will be returned when duplicate value or null are inserted into custId column.
Inserting a duplicate values into Primary Key column
Lets try to insert duplicate value on primary key column custId. First we will insert a record in table then try to insert duplicate value for custid column means will insert one more record in table which will have same value for custId that already exists in column custId.
Lets insert record in table using following insert statement.
INSERT INTO dbo.Customer( CustId, CustName, CustCode, CustMailId)
VALUES
(1, ‘Mukesh Singh’,’Cust_0056′, ‘MukeshS@gmail.com’)
Now we have one record in Customer table as shown below.
SELECT * FROM dbo.Customer
As you can see, CustId =1 already exists in table. Lets try to insert again value 1 for CustId column.
INSERT INTO dbo.Customer( CustId, CustName, CustCode, CustMailId)
VALUES
(1, 'Rajesh Kumar','Cust_00545', 'RajeshKuamrgmail.com')
It gives an error that cannot insert duplicate values, which means primary key does not allow duplicate values.
Inserting null values into Primary Key column
Now lets try to provide null value to primary key column custId.
INSERT INTO dbo.Customer( CustId, CustName, CustCode, CustMailId)
VALUES
(NULL, 'Rajesh Kumar','Cust_00545', 'RajeshKuamr@gmail.com')
It gives an error says ‘Cannot insert the value NULL into column ‘CustId’, table ‘dbo.Customer’; column does not allow nulls. INSERT fails.’ Which means we cannot insert null values in primary key column .
Lets see what is composite key, and how to create a coposite key on table column.
Creating a Composite key on table column
SYNTATX
create table table_name (
column1 datatype1 ,
column1 datatype1 ,
column1 datatype1 , ..
primary key (column_name1, column_name2,..)
Lets look at an example of Composite key in SQL.
Suppose you have a sample data of customers which contains column as CustName , Cust_booldGroup , CustMailId and you are asked to create a table with primary key column in order to identify each records in table uniquely.
As per given sample data, you simply can not create a primary key either on custname, or cust_bloodgroup as there may be chances that two customers might have same name or blood group.
Now have another option to create a primary key including both custname and cust_bloodgroup columns but it can be a risky that that two customer can have same name and blood group combinations.
So, it will be a better way to create a primary key including all three columns to make a strong primary key that uniquely identify each row in a table.
So in order to create a primary key on table column when you includes more than one column this is called as a composite key.
Basically, this is a approach of choosing a primary key columns as a combinations of multiple columns when one column is not sufficient to identify each rows in a table uniquely.
Lets create a new table named as Customer_New and add composite primary key including columns CustName , Cust_booldGroup , CustMailId
CREATE TABLE Customer_New (
CustName VARCHAR(150) ,
Cust_bloodGroup VARCHAR(4),
CustMailId VARCHAR(30)
PRIMARY KEY(CustName, Cust_bloodGroup, CustMailId)
)
Lets insert a records into customer_new table.
INSERT INTO dbo.Customer_New(CustName, Cust_bloodGroup, CustMailId)
VALUES
('Rocky Patel','AB+', 'RockyPatel@gmail.com'),
('Rocky Patel','AB+', 'Rocky89@gmail.com')
As you can see, table Customer_New have primary key on three columns Custname , Cust_bloodGroup and CustMailId so these column maintain the uniqueness of records in a table.
See, for both rows have same customer and blood group combination while they have different mail id so that’s why these two row can be identified uniquely.
If you want to read more on Primary key and composite key refer post:
Primary keys and Composite key.
