Constraints in Oracle : Part 2

This article explains Foreign Key Constraints in Oracle.

iii) FOREIGN KEY CONSTRAINT (FK)

To define the relationship between two tables (one is called parent and the other one is the child table) connected by columns, a foreign key constraint is used. In this constraint the values of the child table must appear in the parent table, which means that for a foreign key, one table should point to a Primary Key in another table. A table can have multiple foreign keys and each foreign key can have a different referenced table.

To understand the foreign Key clearly let's assume two tables:

  1. CUSTOMER {Cust_ID, Cust_Name, Age, ContactNo, Gender, Address}
  2. VENDOR {Vend_ID, Vend_Name, Cust_ID}

Customer Table
Vendor Table
Example

1. Foreign Key Constraint while using CREATE TABLE statement

Syntax

CREATE TABLE table_name

(

Col1 datatype NOT NULL,

Col2 datatype NOT NULL,

Col3 datatype NOT NULL,

CONSTRAINT FK_Column

FOREIGN KEY (Col1, Col2, Col3)

REFERENCES parent_table (Col1, Col2, Col3)

);

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

CREATE TABLE Customer

(

Cust_ID NUMBER CONSTRAINT Customer_PK PRIMARY KEY (Cust_ID)

Cust_Name varchar2(20),

Age NUMBER,

ContactNO NUMBER,

Gender varchar2(10)

Address varchar2(50)

);

 

CREATE TABLE Customer

(

Vend_ID NUMBER CONSTRAINT Customer_FK FOREIGN KEY (Cust_ID)

REFERENCES Customer(Cust_ID),

Cust_ID NUMBER NOT NULL,

Vend_Name varchar2(20)

);

Method 2: With Out-Of-Line Specification/Table Level

CREATE TABLE Customer

(

Cust_ID NUMBER NOT NULL,

Cust_Name varchar2(20) NOT NULL,

Age NUMBER,

ContactNO NUMBER,

Gender varchar2(10)

Address varchar2(50),

CONSTRAINT Customer_PK PRIMARY KEY (Cust_ID)

);

 

CREATE TABLE Vendor

(

Vend_ID NUMBER NOT NULL,

Cust_ID NUMBER NOT NULL,

Vend_Name varchar2(20) NOT NULL,

CONSTRAINT Customer_FK FOREIGN KEY (Cust_ID)

REFERENCES Customer (Cust_ID)

);
 

Note: Here we created a Primary Key on the customer table, both single and composite called Customer_PK. It consists of one field (Cust_ID). Then we created a Foreign Key called Customer_FK on the vendor table that references the customer table based on the Cust_ID field.

AT MULTIPLE COLUMN LEVEL/Composite Keys


Method 1: With In-Line Specification/Column Level

CREATE TABLE Customer

(

Cust_ID NUMBER CONSTRAINT Customer_PK PRIMARY KEY (Cust_ID)

Cust_Name varchar2(20) CONSTRAINT Customer_PK PRIMARY KEY (Cust_Name),

Age NUMBER,

ContactNO NUMBER,

Gender varchar2(10)

Address varchar2(50)

);

 

CREATE TABLE Vendor

(

Vend_ID NUMBER NOT NULL,

Cust_ID NUMBER CONSTRAINT Customer_Multi_FK FOREIGN KEY (Cust_ID)

REFERENCES Customer(Cust_ID),

Cust_Name varchar2(20) CONSTRAINT Customer_Multi_FK FOREIGN KEY (Cust_Name)

REFERENCES Customer(Cust_Name),

Vend_Name varchar2(20) NOT NULL,

);

Method 2: With Out-Of-Line Specification/Table Level

CREATE TABLE Customer

(

Cust_ID NUMBER NOT NULL

Cust_Name varchar2(20) NOT NULL,

Age NUMBER,

ContactNO NUMBER,

Gender varchar2(10)

Address varchar2(50),

CONSTRAINT Customer_PK PRIMARY KEY (Cust_ID, Cust_Name)

);

 

CREATE TABLE Vendor

(

Vend_ID NUMBER NOT NULL,

Cust_ID NUMBER NOT NULL,

Cust_Name varchar2(20) NOT NULL,

Vend_Name varchar2(20) NOT NULL,

CONSTRAINT Customer_Multi_FK FOREIGN KEY (Cust_ID)

REFERENCES Customer(Cust_ID, Cust_Name),

);

Note: Here we created a Primary Key (composite keys) on the customer table called Customer_PK. It consists of the single field (Cust_ID and Cust_Name) then created a Foreign Key called Customer_Multi_FK on the vendor table that references the customer table based on the Cust_ID and Cust_Name field.

2. Foreign Key Constraint while using ALTER TABLE statement:

Syntax

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

FOREIGN KEY (Col1, Col2, Col3)

REFERENCES parent_table (Col1, Col2, Col3);

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Not allowed in Foreign Key Constraint.

Method 2: With Out-Of-Line Specification/Table Level

ALTER TABLE Vendor

ADD CONSTRAINT Customer_FK

FOREIGN KEY (Cust_ID)

REFERENCES Customer (Cust_ID);

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Not allowed in Foreign Key Constraint.

Method 2: With Out-Of-Line Specification/Table Level

ALTER TABLE Vendor

ADD CONSTRAINT Customer_FK

FOREIGN KEY (Cust_ID, Cust_Name)

REFERENCES Customer (Cust_ID, Cust_Name);

3. DROP A Foreign Key Constraint

Syntax

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Here, we created two foreign keys and if we want to drop them then suppose we want to drop the foreign key called Customer_FK, then execute the following Query:

Example

ALTER TABLE Vendor

DROP CONSTRAINT Customer_FK;

iv) UNIQUE CONSTRAINT (UK)

To restrict the same value in the same columns in multiple rows, the unique key constraint is used. And as the name states, it provides uniqueness for a column or a set of columns. It allows multiple NULLs for some of the fields during creation or alter a table as long as the combination of values are unique. Like a Primary Key Constraint, a Unique Constraint in Oracle cannot contain more than 332 columns.

Example

1. Unique Constraint while using CREATE TABLE statement:

Syntax

CREATE TABLE table_name

(

Col1 datatype NULL/NOT NULL,

Col2 datatype NULL/NOT NULL,

Col3 datatype NULL/NOT NULL,

CONSTRAINT constraint_name UNIQUE (Col1, Col2, Col3)

);

 

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

CREATE TABLE Customer

(

Cust_ID NUMBER CONSTRAINT Customer_Unique UNIQUE (Cust_ID),

Cust_Name Varchar2(20) NOT NULL,

Age NUMBER,

ContactNo NUMBER,

Gender varchar2(10),

Address varchar2(50)

);

Method 2: With Out-Of-Line Specification/Table Level

CREATE TABLE Customer

(

Cust_ID NUMBER NOT NULL

Cust_Name varchar2(20) NOT NULL,

Age NUMBER,

ContactNO NUMBER,

Gender varchar2(10)

Address varchar2(50),

CONSTRAINT Customer_Unique UNIQUE (Cust_ID)

);

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

CREATE TABLE Customer

(

Cust_ID NUMBER CONSTRAINT Customer_Unique UNIQUE (Cust_ID),

Cust_Name Varchar2(20) CONSTRAINT Customer_Unique UNIQUE (Cust_Name),

Age NUMBER,

ContactNo NUMBER,

Gender varchar2(10),

Address varchar2(50)

);

Method 2: With Out-Of-Line Specification/Table Level

CREATE TABLE Customer

(

Cust_ID NUMBER NOT NULL,

Cust_Name Varchar2(20) NOT NULL,

Age NUMBER,

ContactNo NUMBER,

Gender varchar2(10),

Address varchar2(50),

CONSTRAINT Customer_Unique UNIQUE (Cust_ID, Cust_Name)

);

2. Unique Constraint while using ALTER TABLE statement

Syntax

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (Col1, Col2, Col3);

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Not allowed in Unique Constraint.

Method 2: With Out-Of-Line Specification/Table Level

ALTER TABLE Customer

ADD CONSTRAINT Customer_unique UNIQUE (Cust_ID);

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Not allowed in Unique Constraint.

Method 2: With Out-Of-Line Specification/Table Level

ALTER TABLE Customer

ADD CONSTRAINT Customer_unique UNIQUE (Cust_ID, Cust_Name);

3. DROP A Foreign Key Constraint

Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

ALTER TABLE Customer

DROP CONSTRAINT Customer_unique;

v) NOT NULL CONSTRAINT (NN)

A NOT NULL Constraint is used to limit a column to not accept the NULL values, in other words the column in the table cannot be null, it must contain a value and if any null value occurs during insertion/ updation then Oracle throws an exception:

"ORA-01400: cannot insert NULL int [Column Description]"
Or
"ORA-10407: cannot update [Column Description] to NULL"

Example

Note: Here, the condition is that the Customer ID and Customer Name must be entered.

1. NOT NULL Constraint while using CREATE TABLE statement

Syntax

CREATE TABLE table_name

(

Col1 datatype NOT NULL,

Col2 datatype NOT NULL,

Col3 datatype NOT NULL,

);

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

CREATE TABLE Customer

(

Cust_ID NUMBER CONSTRAINT Customer_Cust_ID NOT NULL,

Cust_Name Varchar2(20) NOT NULL,

Age NUMBER,

ContactNo NUMBER,

Gender varchar2(10),

Address varchar2(50),

);

 

Method 2: With Out-Of-Line Specification/Table Level

Not allowed in NOT NULL Constraint.

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

CREATE TABLE Customer

(

Cust_ID NUMBER CONSTRAINT Customer_Cust_ID NOT NULL,

Cust_Name Varchar2(20) CONSTRAINT Customer_Cust_Name NOT NULL,

Age NUMBER,

ContactNo NUMBER,

Gender varchar2(10),

Address varchar2(50),

);

Method 2: With Out-Of-Line Specification/Table Level

Not allowed in NOT NULL Constraint.

2. NOT NULL Constraint while using ALTER TABLE statement

Syntax

ALTER TABLE table_name

MODIFY CONSTRAINT constraint_name NOT NULL;

AT SINGLE COLUMN LEVEL


Method 1: With In-Line Specification/Column Level

ALTER TABLE Customer

MODIFY Cust_ID Customer_Cust_ID NOT NULL;

Method 2: With Out-Of-Line Specification/Table Level

Not allowed in NOT NULL Constraint.

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

ALTER TABLE Customer

MODIFY (Cust_ID CONSTRAINT Customer_Cust_ID NOT NULL

(Cust_Name CONSTRAINT Customer_Cust_Name NOT NULL);

Method 2: With Out-Of-Line Specification/Table Level

Not allowed in NOT NULL Constraint.

3. DROP A Foreign Key Constraint

Syntax

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Example

ALTER TABLE Customer

DROP CONSTRAINT Customer_Cust_Name;

 

Previous article: Constraints in Oracle : Part 1