SQL | Primary Key Constraint
Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command.
PRIMARY KEY constraint
A primary key is a column or a group of columns that uniquely identifies each row in a table. It enforce data integrity in tables.
The Primary Key Constraint in SQL Server is also used to make a relationship with a Foreign Key constraint on another table
Features of primary key
1. A table can have only ONE primary key.
2. Primary keys must contain UNIQUE values, and cannot contain NULL or duplicate values.
3. A primary key cannot exceed 16 columns and a total key length of 900 bytes.
4. The primary key uniquely identifies each row in a table. It is often defined on the identity column.
5. All columns defined within the primary key constraint must be defined as a NOT NULL column.
6. A primary key can be defined on one column or the combination of multiple columns known as a composite primary key.
Define Primary key On a Table (On Single Column)
If the primary key consists of only one column, you can define use PRIMARY KEY constraint as a column constraint:
If clustered or nonclustered is not specified, then a unique clustered index for the primary key column will be created if there no clustered index on the table.
This makes retrieving data faster whenever the primary key column is included in the query.
In case the primary key has two or more columns, you must use the PRIMARY KEY constraint as a table constraint:
Create a Primary Key in an Existing Table
Create Primary Key Using SSMS
Right click on the column in design mode, select 'Set Primary Key' and save
Differences between the primary key and unique key in SQL Server?
Both unique key and Primary Key handles the uniqueness of a column on which they are applied. But,
1. By default in SQL Server the primary key creates a unique clustered index whereas the unique key creates a unique non-clustered index on the column.
2. The primary key column doesn’t allow NULL values whereas the unique key column allows only one NULL value.
