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:
- CUSTOMER {Cust_ID, Cust_Name, Age, ContactNo, Gender, Address}
- 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
Join the conversation! Your thoughts help the community grow.