In SQL, Primary Key constraint is defined on a column or a set of columns whose values uniquely identify all the rows in a table. These columns are referred to as the primary key columns. A primary key column cannot contain NULL values since it is used to uniquely identify rows in a table. The primary key constraint ensures entity integrity.
You can define a primary key constraint while creating the table or you can add it later by altering the table. However, if you define the primary key constraint after inserting rows, the SQL Server will give an error if the rows contain duplicate values in the column. While defining a primary key constraint, you need to specify a name for the constraint. If a name is not specified, the SQL Server automatically assigns a name to the constraint.
If a primary key constraint is defined on a column that already contains data, then the existing data in the column is screened. If any duplicate values are found, then the primary key constraint is rejected. The syntax of applying the primary key constraint when creating table is:
CREATE TABLE table_name
(
Col_name [CONSTRAINT constraint_name PRIMARY KEY
[CLUSTERED|NONCLUSTERED]
Col_name [, col_name [, col_name [, … ] ] ]
)
Where,
In the preceding example of the EmployeeLeave table, you can add a primary key constraint, while creating the table. You can set the EmployeeID and the LeaveStartDate columns of the EmployeeLeave table as a composite primary key. You can use the following statement to apply the primary key constraint:
CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int,
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate),
…
…
…
)
The preceding statement creates the EmployeeLeave table with a composite primary key constraint on EmployeeID and LeaveStartDate. The name of the constraint is cpkLeaveStartDate.
You can define a primary key constraint while creating the table or you can add it later by altering the table. However, if you define the primary key constraint after inserting rows, the SQL Server will give an error if the rows contain duplicate values in the column. While defining a primary key constraint, you need to specify a name for the constraint. If a name is not specified, the SQL Server automatically assigns a name to the constraint.
If a primary key constraint is defined on a column that already contains data, then the existing data in the column is screened. If any duplicate values are found, then the primary key constraint is rejected. The syntax of applying the primary key constraint when creating table is:
CREATE TABLE table_name
(
Col_name [CONSTRAINT constraint_name PRIMARY KEY
[CLUSTERED|NONCLUSTERED]
Col_name [, col_name [, col_name [, … ] ] ]
)
Where,
- Constraint_name specifies the name of the constraint to be created.
- CLUSTERED|NONCLUSTERED are keywords that specify if a clustered or a nonclustered index is to be created for the primary key constraint.
- Col_name specifies the name of the column(s) on which the primary key constraint is to be defined.
In the preceding example of the EmployeeLeave table, you can add a primary key constraint, while creating the table. You can set the EmployeeID and the LeaveStartDate columns of the EmployeeLeave table as a composite primary key. You can use the following statement to apply the primary key constraint:
CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int,
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate),
…
…
…
)
The preceding statement creates the EmployeeLeave table with a composite primary key constraint on EmployeeID and LeaveStartDate. The name of the constraint is cpkLeaveStartDate.