PRIMARY KEY in SQL Server

SQL Server primary key What is that? Create primary key in SQL Server how? Let's learn with Quantrimang.com what you need to know about primary keys in SQL!

Primary Key – What is Primary Key?

In SQL Server, a primary key is a constraint that uniquely identifies each row in a table. It ensures the integrity of the data in the table.

• A table can consist of only one primary key.
• A primary key can be defined on a single column or multiple columns called composite primary key.
• A primary key cannot exceed 16 columns and the total length of the key is 900 bytes.
• The primary key uniquely identifies each row in the table. It is usually identified on the identity column.
• Primary key columns do not allow null or duplicate values. It will throw an error if you do this.
• All columns defined in the primary key must be Not Null columns.
• If clustered or non-clustered is not specified, then a single clustered index for the primary key column will be created if there is no clustered index on the table. This makes data retrieval faster whenever the primary key column is included in the query.

What is a primary key in SQL Server?

To delete a primary key in SQL, you must clearly understand how to use it. Essentially, an SQL primary key is a column (or combination of columns) that uniquely identifies each record in a database table. Primary keys also speed up data access and are used to establish relationships between tables.

Suppose you are developing an application called “Customer Management System” to handle all the customer data of a members-only resort. This data may include detailed personal information, assigned member IDs and other details about membership rights… In all tables created in this database, member IDs are used to differentiate customers. Therefore, this field will be the primary key.

Below is a chart of the customer table containing details of the customer's personal information. As you can see, the primary key is identified on the CUST_ID column. Using the primary key, you can retrieve a unique record for any customer. The primary key can be defined with the CREATE TABLE command or the ALTER TABLE command.

Create primary key – using CREATE TABLE command

Syntax for creating primary key using CREATE TABLE command

CREATE TABLE 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)
);

Example of creating primary key using CREATE TABLE command

CREATE TABLE nhanvien
( 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 one field, nhanvien_id. Alternatively, you can create a primary key as follows:

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

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

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

In this example, we have created a primary key made up of 2 columns ho and ten. These 2 fields will uniquely identify records in the Nhanvien table.

Create primary key – using ALTER TABLE command

Primary keys can only be created with the ALTER TABLE command in columns that have been defined as NOT NULL. If the column allows NULL values, it will be impossible to add a primary key without deleting or recreating the table.

Syntax for creating primary key using ALTER TABLE command

ALTER TABLE ten_bang
ADD CONSTRAINT ten_rang_buoc PRIMARY KEY (cot1, cot2, … cot_n);

Example of creating primary key using ALTER TABLE command

ALTER TABLE nhanvien
ADD CONSTRAINT nhanvien_pk PRIMARY KEY (nhanvien_id);

In this example, we create a primary key nhanvien_pk for the existing employee table, including field employee_id. Note the field employee_id must have been previously defined as NOT NULL, otherwise the table must be deleted and recreated and the field defined as NOT NULL.

It is possible to create a primary key with more than 1 field as shown in the following example.

ALTER TABLE nhanvien
ADD CONSTRAINT nhanvien_pk PRIMARY KEY (ho, ten);

Primary key for the table Nhanvien includes 2 fields: first name and last name. Both must be defined as NOT NULL.

Delete primary key in SQL Server

Syntax for deleting primary key using ALTER TABLE command

ALTER TABLE ten_bang
DROP CONSTRAINT ten_rang_buoc;

For example, delete the primary key using the ALTER TABLE command

ALTER TABLE nhanvien
DROP CONSTRAINT nhanvien_pk;

The above example deletes the primary key nhanvien_pk in the table Nhanvien.

Disable primary key in SQL Server

Syntax for disabling primary key with ALTER INDEX command

ALTER INDEX ten_rang_buoc ON ten_bang
DISABLE;

For example, disable the primary key using the ALTER INDEX command

ALTER INDEX nhanvien_pk ON nhanvien
DISABLE;

The above example disables the primary key nhanvien_pk in the table Nhanvien.

Enable primary key in SQL Server

Syntax for activating primary key using ALTER INDEX command

ALTER INDEX ten_rang_buoc ON ten_bang
REBUILD;

Example of activating primary key with ALTER INDEX command

ALTER INDEX nhanvien_pk ON nhanvien
REBUILD;

The above example reactivates the primary key nhanvien_pk of table Nhanvien.

Example of creating a primary key for a column

The following SQL command creates a primary key on the “S_Id” column when the Students table is created.

CREATE TABLE students
(
S_Id int NOT NULL PRIMARY KEY,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
)

Create primary key in SQL for multiple columns:

CREATE TABLE students
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)
)

In the above example there is only one primary key (pk_StudentID). However, it forms two columns (S_Id and LastName).

In summary, things to remember about primary keys in SQL Server:

• The primary key enforces the integrity of the objects in the table.
• Primary keys always have unique data.
• The length of the master key cannot exceed 900 bits.
• Primary key cannot have null value.
• There are no duplicate values ​​in the primary key.
• A table can contain only a limited number of primary keys.

Note: When defining a primary key constraint for a table, the database engine automatically creates a separate index for the primary key column.

Advantages of creating SQL Server primary key: The most prominent main advantage is that it helps users access quickly.

Previous article: CREATE TABLE command in SQL Server

Next article: Alter Table command in SQL Server