SQL Primary Key constraint

A Primary Key constraint uniquely identifies each record in a table

Following are some important key points on Primary key:

• A Primary keys column must contain unique values and cannot have null values.
• All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL.
• A table can have only one primary key, which may consist of single or multiple columns. 
• A Primary key can be made up by one or more columns on a table and when it happens, they are called a Composite key.
• You can not  insert duplicate content into the primary key columns.
• When a primary key is created on table column, SQL Server automatically creates a corresponding unique clustered index on column as well.

SYNTAX

CREATE TABLE tablename 
(
col1 datatype1  NOT NULL PRIMARY KEY, 
col2 datatype2 , ...
);

Lets Look at an example of primary key in SQL Server.

Lets Create a table named as Orders which contains a primary key on column OrderId.

Following statement creates a new table Orders with primary key on column OrderId .

CREATE TABLE Orders 
( 
OrderId INT NOT NULL PRIMARY KEY, 
OrderName VARCHAR(50), 
OrderDate DATETIME 
)

Lets check, whether a primary key is created or not on OrderId column.

You can check it using object explorer,  expand the table folders then expand the Orders table and check cloumns and index folders

You can see that a primary key is created on Column OrderId as a key symbol appears on column name also a clustered index is created SQL Server on column OrderId by default as shown below.

You can also check using a SQL Server Sp_Help  Systemn stored procedure.

As you can see, a primary key is created on orderId column also a clustered index details can also be seen there.

DROP a Primary Key from existing table column

You can drop a primary key from column. To drop a primary key you can use  Alter table Drop Constraint Statement to a drop constraint.

Whenever a constraint is created, SQL Server provides a name to the constraint if the name for constraint is not specifed by user when constraint is crerated.

As you can see the result of sp_help stored procedure as shown above, the primary key constraint name is PK__Orders__C3905BCF576EF356.

Lets drop a primary key constraint from table Orders, Following statement uses Alter table Drop Constraint Statement to drop constrainto, and you need to provide constraint name that you are going to drop from column.

ALTER TABLE Orders
DROP CONSTRAINT PK__Orders__C3905BCF576EF356

 

As you can see, Commands compeleted successfully. Lets check to ensure whether the primary key is removed on column OrderId or not.

Lets quickly check in Object explorer.

You can see there is niether any primary key nor any index on column OrderId.

Creating a Primary key on existing column

As you have just removed a primary key on column OrderId, Lets re-create a primary key again on an existing column OrderId .

Following alter table add constraint statement creates a primary key on OrderId column also you can see in this statement we have provided a name for constraint that is pk_orderId.

ALTER TABLE Orders
ADD CONSTRAINT pk_orders_orderId PRIMARY KEY(OrderId)

You can also verify quickly whether a Primary key is created or not, using SQL Server sp_help stored procedure.

As you can see, a primary key is created on table with the given constraint name Pk_orderId.

 

Primary key with NULL values

Primary key does not allow null values,  as you know that Orders table having a primary key on column OrderId. Lets try to insert a null value into this column.

Following Insert statement adds one record into table Orders, for OrderId columns we provide a null value.

INSERT INTO Orders(orderId, OrderName, orderDate) 
VALUES 
(NULL, 'Machine X5', GetDate())

As you can see, It gives an error saying OrderId column does not allow nulls . That means primary key column can not have null values.

Primary key with duplicate values

Primary key column does not allow duplicate values, that means you can insert duplicate values for primary key column.

To demonistrate this, Lets insert one record into table Orders.

INSERT INTO Orders(orderId, OrderName, orderDate)
VALUES
(1, ‘Machine X5’, GetDate())

Now we have one record in table as shown below.

In case, if you try to insert a new record in table Orders with the value for OrderId column 1 which is already existing in table Orders, then it will give an error saying ‘can not insert duplicate values’. As OrderId 1 is already there in table.

INSERT INTO Orders(OrderId, OrderName, OrderDate)
VALUES
(1, 'Computer Dell', Getdate())

Also Read..

Composite Key

Foreign Key constraint

Primary Key VS composite Key

Unique Key constraint