×
☰ See All Chapters

Primary key constraint in Oracle

  1. Primary key uniquely identifies each record in a database table. If primary key constraint is applied on a column then every row in the table must have a primary key and no two rows can have the same primary key. Primary key value can never be null nor can be modified or updated. 

  2. Columns declared as PRIMARY KEY can be declared as NOT NULL & UNIQUE but PRIMARY KEY enforces both NOT NULL & UNIQUE constraints. So there is no reason to declare columns as NOT NULL & UNIQUE which are declared as PRIMARY KEY. 

  3. Primary key helps to relate with other tables using foreign key. Foreign key is studied in next chapter. 

  4. If Columns declared as PRIMARY KEY is declared  as NOT NULL  or UNIQUE then problem arises while creating FOREIGN KEY, so generally don’t declare column as NOT NULL or UNIQUE which are declared  as PRIMARY KEY. 

Syntax to add Primary Key constraint in Oracle

CREATE TABLE <table-name>

(

<column-name1> <data-type(size)> PRIMARY KEY,

<column-name2> <data-type(size)> <constraint>

<column-name2> <data-type(size)> <constraint>

<column-name2> <data-type(size)> <constraint>

);

Example to add Primary Key constraint in Oracle

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT ,

PHONE  NUMBER(10) PRIMARY KEY,

EMAIL VARCHAR(30)

 );

Composite primary key

Sometimes table cannot have any columns with unique values in each record, but it is necessary to define a primary key to relate it to other tables, in such situations we can define composite primary key.

Composite primary key is defined by set of columns which can uniquely identify every row in the table.

Syntax to add Composite Primary Key constraint in Oracle

 

------------Syntax/Approach 1--------------

 

CREATE TABLE <table-name>

(

<column-name1> <data-type(size)> ,

<column-name1> <data-type(size)>,

<column-name1> <data-type(size)

);

 

ALTER TABLE <table-name>ADD

(

CONSTRAINT <Name for Constraint>

PRIMARY KEY (List of  Column Names)

);

 

------------Syntax/Approach 2--------------

CREATE TABLE <table-name>

(

<column-name1> <data-type(size)> <constraint>,

<column-name2> <data-type(size)> <constraint>,

<column-name2> <data-type(size)> <constraint>,

PRIMARY KEY (List of  Column Names)

);

 

------------Syntax/Approach 3--------------

 

CREATE TABLE <table-name>

(

<column-name1> <data-type(size)> <constraint>,

<column-name2> <data-type(size)> <constraint>,

<column-name2> <data-type(size)> <constraint>,

CONSTRAINT <Name for Constraint>

PRIMARY KEY (List of  Column Names)

);

 

Example to add Primary Key constraint in Oracle

 

------------Syntax/Approach 1--------------

 

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  NUMBER(10),

EMAIL VARCHAR(30)

 );

 

ALTER TABLE CUSTOMER ADD

(

CONSTRAINT CUSTOMER_PK

PRIMARY KEY (CUSTOMER_ID,PHONE,EMAIL)

);

 

------------Syntax/Approach 2--------------

 

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT NOT NULL,

PHONE  NUMBER (10)  NULL,

EMAIL VARCHAR(30) UNIQUE,

PRIMARY KEY (CUSTOMER_ID,PHONE)

 );

 

------------Syntax/Approach 3--------------

 

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT NOT NULL,

PHONE  NUMBER (10)  NULL,

EMAIL VARCHAR(30) UNIQUE,

CONSTRAINT CUSTOMER_PK

PRIMARY KEY (CUSTOMER_ID,PHONE,EMAIL );

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


All Chapters
Author