Primary Key is a type of constraint which help us to maintain the integrity. Primary Key in brief is as below :-
- Primary key enforces the entity integrity.
- Primary Key can be created on a column or combination or column.
- Primary Key value/values uniquely identify each row in the table.
- A table can have only one Primary Key constraint.
- Primary key column can not accept null values as well as values must be unique.
- If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
- With Primary Key, by default Clustered Index get created on Primary Key column.
- When we create Primary Key constraint and we didn't specify the name of constraint then SQL Server provide the name of primary key constraint as per below format :-
PK_<table name>_<16 digit alphanumeric character>
for example :- PK__tbl__D8360F734316F928 - There are two way to create the Primary Key at the time of creation of table
- at column level
for example:-
CREATE TABLE tbl ( col INT CONSTRAINT pk_tbl_col PRIMARY KEY ) - at table level
for example:-
CREATE TABLE tbl ( col INT
, col2 INT
, CONSTRAINT pk_tbl_col PRIMARY KEY(col)
) - Syntax to create the Primary Key on existing table
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> PRIMARY KEY(<primary key column>)
GO - Drop the Primary Key
ALTER TABLE <table name>
DROP CONSTRAINT <constraint name>;
GO
