SQL- How to Add Primary Key to Table

Primary key constraint implements existential integrity. It means that with the help of a primary key the DBMS will always return an individual record from the table. This is possible only if primary key column(s) does not contain any duplicate values or NULL value (unavailable, undefined). A primary key constraint can be said to be combination of UNIQUE and NOT NULL constraints. Let’s see how to add primary key to table

How to Add Primary Key to Table

In a table a primary key can be added as a single column or a combination of columns. In case you define a combination of columns as primary key, then none of the individual columns can allow null values. For example if you take address as a primary key made from combination of columns house no, street, city, state and zip code then none of these columns should allow storing null values. Although two records can have duplicate values like two different addresses can have same value in the city column of the composite primary key.

Primary key applied on single column is called Simple Primary Key and a multiple column primary key is called a Composite Primary Key. The Simple Primary Key can be defined as Column Level constraint or Table level constraint. A composite Primary key can only be defined as a Table Level constraint.

Column Level Primary Key Syntax (Simple Primary Key)

Column-Name DATATYPE( size) [CONSTRAINT constraint-name] PRIMARY KEY

  • Column-Name is the name of the column that must be declared as primary key
  • DATATYPE- what kind of data will be stored in column
  • Size- the number of characters or digits (not for DATE data type)
  • Constraint-name- the name of the primary key constraint (this is optional)
  • PRIMARY KEY keywords

Example- without constraint name

CREATE TABLE SalesTbl
(
SPID number(6) PRIMARY KEY,
SPNAME varchar2(30),
SPCITY varchar2(30) DEFAULT 'New York',
SPCOMM number(2,2)
);

Example- with constraint name

CREATE TABLE SalesTbl
(
SPID number(6) CONSTRAINT pk_spid PRIMARY KEY,
SPNAME varchar2(30),
SPCITY varchar2(30) DEFAULT 'New York',
SPCOMM number(2,2)
);

Table Level Primary Key constraint Syntax (Simple and Composite Primary Key)

In this type the constraint is added at the end of table definition after the last column definition.

(Table definition)

CONSTRAINT [constraint-name] PRIMARY KEY (Column-Name 1, column-name 2…))

  • Constraint-name- the name of the primary key constraint (this is optional)
  • PRIMARY KEY keywords
  • Column-Name(s) are the name(s) of the column(s) that must be declared as primary key

 Example – Table level Simple Primary Key (Single Column)

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustCity varchar2(30),
CustPhone varchar2(15),
CustCreditScore number(4),
CONSTRAINT pk_custid PRIMARY KEY (CustID)
);

Example – Table level Composite Primary Key (Multiple Columns)

CREATE TABLE OrdersTbl
(
OrderID number(4),
CustID number(4),
SPID number(6),
SaleAMT number(8,2),
OrderDate date,
DeliDate   date,
CONSTRAINT pk_orders PRIMARY KEY (OrderID,CustID,SPID)
);

Point to Remember

  • A table can have only one primary key
  • Naming user defined constraints is a good practice since you give names that you can recall later. If you don’t give names to the constraints they will be named starting with SYS_Nnumber where number is an auto generated number created by Oracle system. It will be difficult to  locate the constraints in data dictionary if you need to drop them. While learning how to add primary key to table, you must understand the purpose of constraint naming.