CODEWITHSUNDEEP
sqlsql-serverprimary-keyunique-constraint
tayyab vohra
tayyab vohra

Reputation: 83

Difference Between Unique And Composite Primary key In sql server

I want to know what is the difference between unique key and composite primary key in SQL Server.

According to w3c school:

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

We can create composite primary key by using this:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

For composite primary key syntax:

CREATE TABLE Persons
(
P_Id int,
C_Id int,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
Primary Key (P_Id,C_Id)
);

Upvotes: 3

Views: 21523

Answers (5)

Murugan V
Murugan V

Reputation: 9

Composite Key - Combine multiple columns as single primary key to maintain it’s Collective uniqueness when the columns are combined that will look unique but separately it’s non-unique ( e.g In a school we have n numbers of students named Ram but there’s only one Ram with that DOB, Parent name and other constraints ) this can be done by using composite key.

Unique Key - on the other hand maintains a single/multiple columns UNIQUE.

Primary Key - Single Unique Primary Key for the table.

Note: we can have duplicate values in each column (Composite Key) but as a whole it has to be UNIQUE

Upvotes: 0

Yori
Yori

Reputation: 21

The primary key does not accept the any duplicate and NULL values. A primary key of one table can be referenced by foreign key of another table. A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column. Unique constraints are also referenced by the foreign key of another table. A composite key is having two or more attributes that together can uniquely identify a tuple in a table. Such a key is also known as Compound Key, where each attribute creating a key is a foreign key in its own right.

Upvotes: 1

Jithin Joy
Jithin Joy

Reputation: 136

The UNIQUE constraint uniquely identifies each record in a database table. This provide a guarantee for uniqueness for a column or set of columns. We can define(point) a single row with this.

A PRIMARY KEY has a UNIQUE constraint by default.

While in some tables, there won't be any columns with a unique value to define a row. In such cases COMPOSITE KEYs are used. In such cases two or more columns are combined together so that this combination is unique.

Upvotes: 8

Eralper
Eralper

Reputation: 6612

Composite keys are formed of more than one column

Primary key prevents a second row on that table with same key column values.

A Primary key can be formed of a single column and as well as of more than one column. So primary key can be defined as a composite key too.

Upvotes: 0

Robert Kock
Robert Kock

Reputation: 6008

As you said yourself, the only difference between a unique and a primary key is that there may only be 1 primary key on a table while it can have more unique keys.
Furthermore, the values of a primary key may not be null.
Both unique- and primary keys can be composed of multiple columns (composed key).

By the ways, in your example you had a unique key on the P_Id column and a composed primary key that includes that very same column.
This has no sense.
I would suggest to create only a simple primary key on that P_Id column.

Upvotes: 1

Related Questions