The PRIMARY KEY constraint uniquely identifies each record in a database table.

Following rules and suggestions are applied to the PRIMARY key:

  • Primary keys must contain unique values.
  • A primary key column cannot contain NULL values.
  • Each table should have a primary key, and each table can have only ONE primary key.

In the example below inside the 'EMP' table, empno is the primary key, which is unique for all customers. If we wish to relate two tables, primary key plays a vital role. Let's explore how:

PRIMARY KEY CONSTRAINT ON CREATE TABLE:

CREATE TABLE customer (Cust_id NUMBER(2) NOT NULL PRIMARY,

   LastName VARCHAR2(14),

   FirstName VARCHAR2(14) NOT NULL,

   Address VARCHAR2(20),

   Telno NUMBER(20)

   );

Next example shows how to name PRIMARY constraints and defining a constraint on multiple columns:

CREATE TABLE customer (Cust_id NUMBER(2) NOT NULL,

   LastName VARCHAR2(14),

   FirstName VARCHAR2(14) NOT NULL,

   Address VARCHAR2(20),

   Telno NUMBER(20),

   CONSTRAINTS pk PRIMARY KEY (cust_id, LastName)

   );

And this example will show us how to add a PRIMARY constraint to already existing table:

ALTER TABLE customer ADD PRIMARY KEY (cust_Id);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns:

ALTER TABLE customer ADD CONSTRAINT pk_custidlname PRIMARY KEY  (Cust_id,LastName);

And in order to DROP a PRIMARY KEY Constraint, we do this:

ALTER TABLE customer DROP CONSTRAINT pk_custidlname;