MS SQL Server: Primary Key PRIMARY KEY in SQL Server

Learn how to create, delete, disable or enable primary keys in SQL Server with syntax and examples.

What is the primary key in SQL Server?

In SQL Server (Transact-SQL), the primary key is a field or a combination of fields and is defined as a single record. No field in the primary key contains a NULL value. A table has only one primary key. The primary key can be defined with the CREATE TABLE command or the ALTER TABLE command.

Create the primary key - with the CREATE TABLE command

The main key generation syntax is with the CREATE TABLE command

 CREATE TAB LE ten_bang 
 ( 
 cot1 kieu_du_lieu [ NULL | NOT NULL ] [ PRIMARY KEY ], 
 cot2 kieu_du_lieu [ NULL | NOT NULL ], 
 . 
 ); 

or

 CREATE TABLE ten_bang 
 ( 
 cot1 kieu_du_lieu [ NULL | NOT NULL ], 
 cot2 kieu_du_lieu [ NULL | NOT NULL ], 
 … 
 CONSTRAINT ten_rang_buoc PRIMARY KEY (cot1, cot2, … cot_n) 
 ); 

The primary key generation example is with the CREATE TABLE command

 CREATE TABLE nh anvien 
 ( nhanvien_id INT PRIMARY KEY, 
 ho VARCHAR(50) NOT NULL, 
 ten VARCHAR(50), NOT NULL, 
 luong MONEY 
 ); 

In this example, the primary key for the nhanvien table is made up of a field called nhanvien_id. In addition, the primary key can be created as follows:

 CREATE TABLE nhanvi en 
 ( nhanvien_id INT, 
 ho VARCHAR(50) NOT NULL, 
 ten VARCHAR(50), NOT NULL, 
 luong MONEY 
 CONSTRAINT nhanvien_pk PRIMARY KEY (nhanvien_id) 
 ); 

Now is the example of creating a primary key with more than 1 field in SQL Server.

 CREATE TABLE vn ien 
 ( ho VARCHAR(50) NOT NULL, 
 ten VARCHAR(50), NOT NULL, 
 luong MONEY 
 CONSTRAINT nhanvien_pk PRIMARY KEY (ho, ten) 
 ); 

In this example, we created the primary key made up of two columns, ho and ten. These two fields will uniquely identify the record in the table.

Create a primary key - with the ALTER TABLE command

Primary keys can only be created with the ALTER TABLE command in columns defined as NOT NULL. If the column allows NULL values, the primary key cannot be added without deleting or recreating the table.

The syntax for creating a primary key is with the ALTER TABLE command

 ALTER TABLE ten_ bang 
 ADD CONSTRAINT t en_rang_buoc PRIMARY KEY (cot1, cot2, . cot_n); 

Example of creating a primary key with the ALTER TABLE command

 ALTER T ABLE nhanvien 
 ADD CON STRAINT nhanvien_pk PRIMARY KEY (nhanvien_id); 

In this example, we create the primary key for the available table, including the nhanvien_id field . Note that the nhanvien_id field must be defined as NOT NULL before, otherwise it will have to be deleted and re-created the table and define this field as NOT NULL.

You can create a primary key with more than 1 field as in the example below.

 ALTER TABLE staff 
 ADD CONSTRAINT n hanvien_pk PRIMARY KEY (cough, ten); 

The primary key for the table includes 2 fields, namely first and last name. Both must be defined as NOT NULL.

Delete the primary key in SQL Server

The syntax for deleting the primary key is with the ALTER TABLE command

 ALTER TAB LE ten_bang 
 DROP CONS TRAINT ten_rang_buoc; 

Example of deleting the primary key with the ALTER TABLE command

 ALTER TABLE staff 
 DROP CONSTRAINT nhanvien_pk; 

The above example deletes the primary key in the table.

Disable the primary key in SQL Server

The syntax to disable the primary key with the ALTER INDEX command

 ALTER IN DEX ten_rang_buoc ON ten_bang 
 DISABLE; 

As an example,, disable the primary key with the ALTER INDEX command

 ALTER INDEX nhanvien_pk ON nhanvien 
 DISABLE ; 

The example above disables the primary key in the table.

Activate the primary key in SQL Server

The syntax for activating the primary key is with the ALTER INDEX command

 ALT ER INDEX ten_rang_buoc ON ten_bang 
 REBU ILD;