Add Primary Key in SQL Server

One of the cirtical factor in database/table designing is to prevent duplicate records. There are several ways to do this. You can place Unique Index or Unique Constraint or add a primary key. Well there is only slight difference between these things.

I will go one by one to describe about these but for now lets concentrate on adding a primary key to a table.

There are two ways to add a primary key constraint:

1) While Creating the Table using CREATE TABLE command.
2) Adding Primary key after table creation using ALTER TABLE statement.

The simple thing is to create the table first and define the Primary key later. Again each one of us will have different opinion but to me this is simple step. Though you will end up in having multiple SQL statements but you will have more control on what you are doing.

The pre-requisite to define a Primary Key on a column is, that column should be NOT Nullable.
Check out Adding NOT NULL constraint article to meet the pre-requisite of Primary key.

To verify whether the column is NOT NULL or not, run the below query:

SELECT TABLE_NAME,COLUMN_NAME,IS_NULLABLE FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'DEPT'

If IS_NULLABLE is "NO" means column has Not Null Constraint.


Lets say I want to add Primary Key constraint on DEPTNO Column.

ALTER TABLE:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

We are trying to add a Primary Key Constraint on DEPT table and the column is DEPTNO.
The Name of the Constraint is PK_DEPT. Its very important to give a name to the constraint.
As the general standards Primary key constraint name should always start with PK_ and followed by the table Name Or you can have _PK.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO INT PRIMARY KEY, DNAME VARCHAR(30))

As you can see i didn't specify constraint name to Primary key. So Tell me is there any way to put a constraint name in the CREATE TABLE statement??