SQL Server - How to create Primary Key Constraint?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

The basic funda is that A Not null constraint restrict the insertion of null values into a column. 
Primary Key Constraint is another feature of SQL which uniquely identifies each row in the table. It must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table and a table can have only one primary key but you can create a primary key on one or more of the columns of a table.

How can we create Primary Key constraints?

A table can have a Primary Key Constraint. We can create Primary Key constraints at two levels -

Column Level Primary Key Constraints - We can define Primary Key constraint with CREATE TABLE statement or in time with table definition. 

For example, the below query creates a table Employee with the column fields EmpId with Primary Key constraint, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint and IsActive column with Default value. Thus, Age column has some specific check constraint to validate the data before inserting into the data table.

---- Column Level Primary Key Constraint CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL Primary Key, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL CHECK(Age>=18), MobileNo Bigint Unique, IsActive Bit Default(1) ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age, MobileNo) VALUE (101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind that if you are not going to assign a name for primary key constraint, unique constraint, check constraint and default constraint then system/SQL server will generate static constraint names for the primary key, unique, check and default constraint column.

Table Level Primary Key Constraints -

You can create a primary key constraint after creating the table with the help of Alter Table command as given below.

---- Table Level - Primary Key being added to another column ALTER TABLE TBL_EMPLOYEE ADD Constraint  PK_Emp_EmpId Primary Key (EmpId) ---- drop Primary Key Constraint ALTER TABLE TBL_EMPLOYEE Drop Constraint  PK_Emp_EmpId

What happen, if you have some data into your table?

If you have some data into your table, for example in EmpId field you have data such as Null and you want to apply primary key constraint on this column. Then you cannot create Table level primary key constraint because primary key constraint will not allow any null value. 

To watch a live demo, please visit our YouTube Channel -