SQL – Foreign Key and its use in Database

In relational databases, a Foreign Key is a crucial concept that helps establish a connection between two tables. It plays a significant role in ensuring data integrity by enforcing referential constraints between records in different tables. By linking one table’s field to the primary key of another table, foreign keys create relationships that allow for efficient data retrieval and management. In this article, we’ll explore the importance of foreign keys, how they work, and their practical applications in maintaining the consistency of your database.

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.

Foreign Key vs. Primary Key:-

1.Definition:

• Primary Key: A Primary Key is a column (or a combination of columns) in a table that uniquely identifies each row. It ensures that no two rows can have the same primary key value, and it cannot contain NULL values.
• Foreign Key: A Foreign Key is a column (or a combination of columns) in a table that creates a link between two tables. It refers to the Primary Key in another table, establishing a relationship between the data.

2.Purpose:

• Primary Key: The primary purpose of a primary key is to uniquely identify each record in the table. It guarantees the entity’s uniqueness and prevents duplicate entries.
• Foreign Key: The foreign key’s purpose is to create a relationship between two tables by referencing the primary key in another table. It enforces referential integrity, ensuring that the data across related tables remains consistent.

3.Uniqueness:

• Primary Key: A primary key must have unique values for each row in the table.
• Foreign Key: A foreign key does not require uniqueness. Multiple rows in the child table can refer to the same value in the parent table.

4.Nullability:

• Primary Key: Cannot contain NULL values. Every row must have a value for the primary key.
• Foreign Key: Creates a relationship between two tables, often representing a parent-child relationship, where the foreign key in the child table refers to the primary key of the parent table.

5.Role in Data Integrity:

• Primary Key: Ensures entity integrity by uniquely identifying each record in the table.
• Foreign Key: Ensures referential integrity by making sure that the relationships between records in different tables remain valid.

Both Primary Keys and Foreign Keys are essential in relational databases, each serving a different yet complementary role in organizing and maintaining the integrity of data across tables.