Learn what is the importance of primary key, why we should use it and how to create Alter and drop the primary key in SQL Server.
Introduction
Learn what is the importance of primary key, why we should use it and how to create Alter and drop the primary key in SQL Server.
Primary Key in SQL Server
Primary Key is a column or group of columns* which identify each row uniquely, doesn`t accept null values and use to maintain referential integrity.
*primary key with more than one column called composite key.
Why use primary key:
Identify Record Uniquely:
Enforcing Primary key on column(s) help user to avoid duplicate data in tuples (Rows). This helps identifying each data uniquely and it avoids duplication in the record. Example in school/collage each student has a roll number which identifies one student only.
Enforce Referential Integrity:
It helps in creating a parent child relationship using primary and foreign key.
Create Cluster Index:
Indexing helps in fast searching of row(s) in the table. Every book has an index page before its content to help reader directly jump to the desired chapter, that exactly index does for the table helps to find the record.
Create Primary Key Syntax:
Create Table Table_Name (Column_Name DataType Primary Key ,Column_name2 DataType )
If you want to name the constraint
Create Table Table_Name (Column_Name DataType ConstraintName Primary Key ,Column_name2 DataType )
Let us do some practical work and create a table with primary key with constraint name.
Create Table [TableWithPK] (ID Int CONSTRAINT PK_ID PRIMARY KEY , name Varchar(10) not null ,Country Varchar(10) )
Now we have a table name [TableWithPK] in the database contains primary key on the id column. If you want to check table`s property, you can use the command:
SP_HELP 'Table_name'
It shows every detail of the table, let`s check the property
of your table.
This command shows everything about the table, like all columns in the table, data types , primary key and its name etc.
If you have already created a table and now want to make one or more columns as primary key, you can create it with the Alter table command. Let us have a look on below example:
Alter Table Add Primary Key Syntax:
Alter Table TableName Add Constraint PrimaryKeyName Primary Key (column)
In the above example, we have already created a primary key on the ID column, and we cannot create two primary key on one table, so to continue the example we will first drop the primary key and then create again with the Alter table command.
Drop Primary Key In SQL Server Syntax:
Alter Table TableName Drop Constraint PrimaryKeyName
Let us use the above syntax in the example:
ALTER TABLE [TableWithPK] DROP CONSTRAINT PK_ID
ALTER TABLE [TableWithPK] add CONSTRAINT PK_ID primary key (ID)
Please not you can`t make the primary key to a column which is Nullable.