Difference Between Unique And Composite Primary key In sql server

Question

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)
);

Solution

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.