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

Answer 1

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.

Answer 2

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.