Constraints in Oracle : Part 2

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}

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