Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the PRIMARY KEY integrity constraint in SQL.
What is PRIMARY KEY in SQL Server?
A PRIMARY KEY is the hallmark of a RDBMS. The PRIMARY KEY uniquely identifies every row in a table (called a tuple) and allows us to create relations between the different tables in a database making a DBMS relational.
A primary key is defined on a column in a table. Since a primary key uniquely identifies a tuple a primary key column cannot have NULL or duplicate values. Therefore, SQL automatically enforces the NOT NULL constraint on a PRIMARY KEY column even if it is not specified explicitly during table creation. SQL also does not allow entry of duplicate values in a primary key column and an attempt to do so fails with integrity constraint error.
It is also possible to designate more than one column (i.e. two or more columns) as the PRIMARY KEY. Such a PRIMARY KEY is also referred to as a COMPOSITE KEY. It might be required to define a COMPOSITE KEY if none of the columns in a table satisfies the non-null and unique constraint conditions. In such a case two or more columns together can be combined as the PRIMARY KEY. An example can be to use both the first name and last name columns as the PRIMARY KEY since there might be duplication for either but both combined ensure uniqueness.
Operation
A PRIMARY KEY can be defined on a column or multiple columns while creating a table or later. It is also possible to drop a PRIMARY KEY in a table if not required.
SQL Server PRIMARY KEY Syntax
The basic syntax of adding PRIMARY KEY using CREATE TABLE and ALTER TABLE statements and dropping PRIMARY KEY using DROP TABLE statement is given below.
CREATE TABLE Syntax
Single column Primary Key
The first CREATE table syntax is without using a constraint name and the second syntax is with a constraint name. Both are valid. It is good practice to use a constraint name for easy identification and management.
CREATE TABLE tablename (column1 DATATYPE PRIMARY KEY, column2 DATATYPE, …………………………………);
CREATE TABLE tablename (column1 DATATYPE, …………………………………, CONSTRAINT constraint_name PRIMARY KEY column1);
Multiple column Primary Key (Composite Key)
The first CREATE table syntax is without using a constraint name and the second syntax is with a constraint name. Both are valid. It is good practice to use a constraint name for easy identification and management.
CREATE TABLE tablename (column1 DATATYPE, column2 DATATYPE, …………………………………, PRIMARY KEY (column1, column2));
CREATE TABLE tablename (column1 DATATYPE, column2 DATATYPE, …………………………………, CONSTRAINT constraint_name PRIMARY KEY (column1, column2));
Alter Table Syntax
ALTER TABLE tablename ADD PRIMARY KEY (columnname);
ALTER TABLE tablename ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);
Drop Table Syntax
The drop table syntax operates through the constraint name.
ALTER TABLE tablename DROP CONSTRAINT constraint_name;
In this syntax,
- CREATE – clause used to create a database object (like table, index, schema etc.).
- DATATYPE – keyword identifying the type of the data i.e. whether int, string, date etc.
- NOT NULL – optional. Keyword combination to tell SQL that the column cannot have NULL values.
- PRIMARY KEY – optional. Keyword combination to tell SQL that the column or columns that follow are non-null, unique valued columns which should be treated as the KEY for the table.
- CONSTRAINT – optional. Keyword to tell SQL that what follows is an integrity constraint.
- constraint_name – optional. A name to identify the constraint that follows.
- ALTER – clause used to modify a database object (e.g. table, schema, index etc.).
- ADD – clause used to add something to a database object (like adding a column or constraint to a table).
- DROP – clause used to remove something from a database object (like dropping columns or constraints from a table) or to delete a database object altogether (like dropping table).
SQL Server PRIMARY KEY Examples
Let us try to understand the above through some practical examples.
1) SQL Server PRIMARY KEY – single column example
Suppose we want to create a table that will hold the employee data of the company with the employee id being primary key. The following CREATE TABLE statement does the same.
CREATE TABLE employees (emp_id TINYINT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), join_date DATE, designation VARCHAR(50), department VARCHAR(50), salary MONEY);
We can check the successful creation of the table using the following command or from the SSMS (SQL Server Management Studio) GUI.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'employees';
It will show the table structure as below. Notice the key symbol and ‘Allow Nulls’ checkbox unchecked for the the emp_id primary key field.
2) SQL Server PRIMARY KEY – multiple column (composite key) example
We can recreate the employee table again with the following query using the employee first and last name columns together as the primary key instead of employee id.
CREATE TABLE employees (first_name VARCHAR(50), last_name VARCHAR(50), join_date DATE, designation VARCHAR(50), department VARCHAR(50), salary MONEY, CONSTRAINT table_pk PRIMARY KEY (first_name, last_name));
Once done we can check the successful table creation using the above given command or from the SSMS GUI. It will show the table structure as below. Notice the key symbol and ‘Allow Nulls’ checkbox unchecked against the first_name and last_name columns.
Now we can DROP the primary key from the table with the following statement.
ALTER TABLE employees DROP CONSTRAINT table_pk;
After dropping we can check the table structure using above cited command or SSMS GUI and we see that the key icon does not exist against any column anymore.
We can add back the composite primary key using the following ALTER statement and restore the table to its original state. Note that I have used a new constraint name.
ALTER TABLE employees ADD CONSTRAINT reconstructed_pk PRIMARY KEY (first_name, last_name);
After executing the statement, we can check the table structure using above cited command or SSMS GUI and see that the key icons are back again.
