Primary Key Constraint

SQL – Foreign Key and its use Database

by

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:

SQL - Primary Key and its use in a Database

Leave a comment