Summary: in this tutorial, you will learn about the primary key & how lớn use the SQL PRIMARY KEY constraint to lớn add a primary key khổng lồ the table.
Bạn đang xem: Sql primary key constraint
What is the primary key in SQL?
A table consists of columns & rows. Typically, a table has a column or mix of columns whose value uniquely identify each row in the table. This column or the mix of columns is called the primary key.
The primary key that consists of two or more columns is also known as the composite primary key.
See the following courses table.
Because the values in the course_id column uniquely identify the rows in the courses table, the course_id column is the primary key of the courses table.
Each table has one và only one primary key. The primary key does not accept NULL or duplicate values.
In case the primary key consists of two or more columns, the values may be duplicate in one column, but the combination of values from all columns in the primary key must be quality.
See the following training table.
The primary key of the training table consists of the employee_id & course_id columns.
The values in the course_id column are duplicate however the combination of values in the employee_id & course_id are not.
Creating table with primary key
Generally, you define the primary key when creating the table. If the primary key consists of one column, you can use the PRIMARY KEY constraint as column or table constraint. In case the primary key consists of two or more columns, you must use the PRIMARY KEY constraint as the table constraint.
Suppose you want to lớn manage the projects and project assignments of the company in the database. Therefore, you need khổng lồ create two tables: projects & project_assignments.
The following statement creates the projects table:
CREATE TABLE projects ( project_id INT PRIMARY KEY, project_name VARCHAR(255), start_date DATE NOT NULL, end_date DATE NOT NULL);You add the PRIMARY KEY in the column definition to lớn make the project_id column as the primary key of the projects table.
The following statement is equivalent lớn the above sầu statement but instead of using the PRIMARY KEY constraint as the column constraint, it uses the table constraint.
CREATE TABLE projects ( project_id INT, project_name VARCHAR(255), start_date DATE NOT NULL, end_date DATE NOT NULL, CONSTRAINT pk_id PRIMARY KEY (project_id));You use the CONSTRAINT clause at the end of the CREATE TABLE statement to promote the project_id column to lớn the primary key.
To store the project assignments which represents who was assigned to which project, you need khổng lồ create the project_assignments table using the following statement:
CREATE TABLE project_assignments ( project_id INT, employee_id INT, join_date DATE NOT NULL, CONSTRAINT pk_assgn PRIMARY KEY (project_id , employee_id));
Code language: Squốc lộ (Structured Query Language) (sql)Because the primary key consists of two columns: project_id & employee_id, you must use the PRIMARY KEY as the table constraint.
Adding the primary key with ALTER TABLE statement
First, you can define a table without a primary key using the CREATE TABLE statement though it is not a good practice. Then, you add the primary key to the table using the ALTER TABLE statement.
For example, the following statement creates the project_milestones table without the primary key. The project_milesones stores milestones of projects.
Code language: Squốc lộ (Structured Query Language) (sql)Now, you can use the following ALTER TABLE statement to lớn promote the milestone_id column as the primary key.
Code language: SQL (Structured Query Language) (sql)
Removing the primary key constraint
You will rarely remove sầu the primary key of a table. However, in case you must vì chưng it, you can use the ALTER TABLE statement as follows:
Code language: Squốc lộ (Structured Query Language) (sql)If you are using MySQL, the syntax for removing the primary key is simpler as follows:
Code language: Squốc lộ (Structured Query Language) (sql)For example, to lớn remove the primary key constraint of the project_milestones table, you use the following statement.
Code language: SQL (Structured Query Language) (sql)In this tutorial, we have introduced you lớn the primary key concept và shown you how to lớn manage the primary key of a table including adding and removing the primary key.
