Primary key in SQL Server

1.       What is a Primary key ?

Ans: A Primary key constraint is a unique identifier for a row within a database table. It prevents duplicate and ensure that all records have their own distinct values. Primary key don’t allow nulls , so it is guaranteed that each record has its own unique populated value.

Some other features are:

a)      It uniquely identify each row in a table.

b)      It is used to enforce entity integrity.

c)       Each table should have a primary key, and each table can have only ONE primary key.

Note: It is not possible to change the length of a column defined with a Primary Key constraint. And if you need to change the length, then first delete the existing Primary Key Constraint and then re-create it with a new definition.

Example :

A.      Create primary key while creating a table:

CREATE TABLE Student
(
S_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)
)

In the example above there is only ONE PRIMARY KEY (pk_StudentID). However, the value of the pk_StudentID is made up of two columns (S_Id and LastName).

B.      Create primary key while altering a table:

ALTER TABLE Student
ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)

Note : Before altering the table make sure that the fields like S_Id and LastName should not accept NULL values.

C.      To Drop a Primary Key:

ALTER TABLE Student
DROP PRIMARY KEY

Thanks For Reading..!!

Default Programmer