What is primary key in a table and How to create it ?

Primary key is one of the constraints in SQL server which enforces the Unique value for each row in a table.

A table can have only one primary key.The column which act as a primary key will not allow null values.Usually the primary key columns are defined on identity columns.

Create Syntax : 

CREATE TABLE [TABLENAME]

(

    [COLUMNNAME] [DATETYPE] [CONSTRAINT],

    [COLUMNNAME1] [DATETYPE] [CONSTRAINT],

    .

    .

    .

    [COLUMNNAME(N)] [DATETYPE] [CONSTRAINT],

)

Example :

CREATE TABLE dbo.Department

(

    [ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [NAME] VARCHAR(10) NULL, 

    [AGE] [INT] NOT NULL

)

If we use the above method then the primary key will be created by the database engine.

If you want to create the primary key on user defined name ,then use the below method.

Example :

CREATE TABLE dbo.Department

(

    [ID] [INT] IDENTITY(1,1) NOT NULL ,

    [NAME] VARCHAR(10) NULL, 

    [AGE] [INT] NOT NULL,

    CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([ID]  ASC)    

)

Now the primary key will be created in the name of PK_ID.

Now we created the constraint in ASC order,so it will always order the data in ascending order.