PRIMARY KEY (SQL) Constraint

A key is a column or combination of columns that help identify specific row in a table. A primary key in sql is a column or set of columns that uniquely identify a record from the other records in the database. PRIMARY KEY in SQL is actually a combination of the UNIQUE and NOT NULL constraints. It is impossible to distinguish between rows in a table without a primary key in SQL, because rows may contain duplicate values. Primary key in SQL cannot be defined on columns that contain duplicate values or NULL values. Primary key also does not allow entering a duplicate value/NULL values in a primary key column.

ADDING A PRIMARY KEY
Primary key in SQL can be added using

1. Create Statement
2. Alter Statement

 

Adding Primary Key using CREATE:

Example:
The following query will define primary key in SQL on column dept_no of department table using CREATE statement:

CREATE TABLE department (dept_no NUMBER(2) PRIMARY KEY, dept_name VARCHAR2(15), mgr_id NUMBER,  mgr_start_date DATE);

Example 2:
The following query is same to one above except that it defines primary key constraint with name pk_dept_no

CREATE TABLE department (dept_no NUMBER(2) CONSTRAINT pk_dept_no PRIMARY KEY, dept_name VARCHAR2(15), mgr_id NUMBER,  mgr_start_date DATE);

Adding Primary Key using ALTER:

Example:
The following query will define primary key in SQL on dept_no column of department given that there is no primary key existed earlier:

ALTER TABLE department ADD PRIMARY KEY (dept_no);

Example 2:
The following query is same to one above except that it defines primary key constraint with name pk_dept_no

ALTER TABLE department ADD CONSTRAINT pk_dept_no PRIMARY KEY (dept_no);

DROPPING A PRIMARY KEY
Example:
The following statement will drop primary key in SQL from department table:

ALTER TABLE department DROP PRIMARY KEY CASCADE;

Note: CASCADE will remove any referential constraints (FOREIGN keys) defined based on this Primary Key in SQL. So be very careful when you remove Primary Key in SQL from a table.

Example 2:
The following statement will drop primary key in SQL identified by its name:

ALTER TABLE department DROP CONSTRAINT pk_dept_no;

Primary keys in SQL ensure data integrity in a table by requiring unique key values in each record of the primary key. This means columns in a primary key can not contain duplicate or null values. Each table may only have one primary or unique key. A primary key in SQL cannot be renamed.