In this article you will learn about-
- What is a foreign key?
- How to create foreign key constraint?
- How to drop foreign key constraint?
What is foreign Key?
- A foreign key is a constraint which allows to specify that a column in a table refers to the primary key of another table.
- A foreign key is a way to enforce referential integrity within your Oracle database.
- The referenced table is called the parent table while the table with the foreign key is called the child table.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
Syntax:
The syntax for creating a foreign key using a CREATE TABLE statement is:
CREATE TABLE child_table (
column1 data_type,
column2 data_type,
...
CONSTRAINT constraint_name
FOREIGN KEY (column1,column2,.. of child_table)
REFERENCES parent_table (column1,column2,..)
);Example:
CREATE TABLE "BMS"."DISPATCH"
( "DOD" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"SHIFT" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"HEATID" VARCHAR2(8 BYTE) NOT NULL ENABLE,
"WAGON_NO" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"CHALAN_NO" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"DO_NO" VARCHAR2(20 BYTE),
"DESTINATION" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"STEELGRADE" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"BAY" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"DISPATCH_COUNT" VARCHAR2(20 BYTE) NOT NULL ENABLE,
CONSTRAINT "DISPTACH_FK" FOREIGN KEY ("HEATID")
REFERENCES "BMS"."STOCK" ("HEATID") ENABLE
)Parent Table – STOCK
CREATE TABLE "BMS"."STOCK"
( "DOE" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"HEATID" VARCHAR2(8 BYTE),
"CASTER_NO" NUMBER(2,0) NOT NULL ENABLE,
"FORMAT" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"LENGTH" NUMBER(3,0) NOT NULL ENABLE,
"STEELGRADE" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"PRODUCTION" NUMBER(4,0) NOT NULL ENABLE,
"MN_BAY_COUNT" NUMBER(4,0),
"NP_BAY_COUNT" NUMBER(4,0),
"PQ_BAY_COUNT" NUMBER(4,0),
"QR_BAY_COUNT" NUMBER(4,0),
"TONNAGE" NUMBER(10,3),
CONSTRAINT "STACK_PK" PRIMARY KEY ("HEATID")
);Using an ALTER TABLE statement
Syntax:
ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1,column2,....)
REFERENCES parent_table (column1,column2,....);Example:
ALTER TABLE dispatch
ADD CONSTRAINT Disptach_FK
FOREIGN KEY (Heatid)
REFERENCES stock (Heatid);Droping a foreign key constraint
To drop a foreign key, use following statement-
Syntax:
ALTER TABLE child_table
DROP CONSTRAINT constraint_name;Example:
ALTER TABLE dispatch
DROP CONSTRAINT Disptach_FK;Points to note:
- You can’t insert a record in child table if it is not in parent table.
- You can’t delete a record from parent table without deleting it from child table.
Kindly comment and share if you liked the article.
Related posts:
Hello, my name is Deependra Singh. I hold B.tech degree in Computer Science & Engineering from Institute of Engineering & Technology, Lucknow. Currently, I am working in Steel Authority of India Limited as a Software Developer.