What is a Foreign Key?

A foreign key is a constraint used to link one table to another. It maintains referential integrity between two tables by ensuring that the value in a foreign key column(s) corresponds to a value in the primary key of another table. A foreign key can consist of one or more columns, and it prevents actions that would break the link between the tables, such as deleting referenced data in the primary table.

It establishes a relationship between two tables:

• References a Primary Key: A foreign key in one table corresponds to the primary key in another table, linking the data between them.
• Maintains Referential Integrity: Ensures that foreign key values in the dependent table always refer to existing values in the referenced table.
• Prevents Data Inconsistencies: Restricts operations, like deletion or updating, that would result in orphaned records.

Declaring a Foreign Key

A foreign key can be defined during table creation using the FOREIGN KEY constraint. This constraint specifies that a column (or set of columns) in the table references the primary key of another table.

Syntax -

CREATE TABLE table_name (
column1 data_type [NOT NULL],
column2 data_type [NOT NULL],
...
FOREIGN KEY (foreign_key_column) REFERENCES referenced_table (primary_key_column)
[ON DELETE CASCADE | SET NULL | NO ACTION]
)
IN database_name.tablespace_name;

• table_name: Name of the table containing the foreign key.
• foreign_key_column: Column(s) in the table that act as the foreign key.
• REFERENCES referenced_table: Specifies the table that the foreign key references.
• primary_key_column: Column(s) in the referenced table that are linked to the foreign key.
• ON DELETE: Specifies the action to take when the referenced row is deleted in the primary table:
  • CASCADE: Deletes rows in the dependent table if the referenced row is deleted.
  • SET NULL: Sets the foreign key to NULL if the referenced row is deleted.
  • NO ACTION: Prevents deletion in the primary table if there are dependent rows.

Examples - Defining a Foreign Key on a Table

CREATE TABLE EMPLOYEE (
EMP_ID INTEGER NOT NULL,
EMP_NAME CHAR(50),
DEPT_ID INTEGER,
PRIMARY KEY (EMP_ID),
FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPT_ID) ON DELETE CASCADE
)
IN EMPLDB.EMPLTS;

• EMPLOYEE: Creates a table named EMPLOYEE.
• DEPT_ID: Defines DEPT_ID as a foreign key, referencing DEPT_ID in the DEPARTMENT table.
• ON DELETE CASCADE: Ensures that if a department is deleted in DEPARTMENT, all employees associated with that department in EMPLOYEE are also deleted.

Altering a Foreign Key

To modify a foreign key constraint, you need to:

1. Drop the existing foreign key.
2. Add a new foreign key with the modified definition.

Dropping the Foreign Key

To remove an existing foreign key, use the ALTER TABLE statement with DROP FOREIGN KEY.

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

Adding a New Foreign Key

After dropping the foreign key, you can add a new one using ALTER TABLE with ADD FOREIGN KEY.

ALTER TABLE table_name
ADD FOREIGN KEY (foreign_key_column) 
REFERENCES referenced_table (primary_key_column)
[ON DELETE CASCADE | SET NULL | NO ACTION];

Examples - Altering a Foreign Key on a Table

Suppose we need to change the DEPT_ID foreign key constraint on the EMPLOYEE table to set DEPT_ID to NULL when the referenced row is deleted in the DEPARTMENT table.

Dropping the Foreign Key

ALTER TABLE EMPLOYEE
DROP FOREIGN KEY DEPT_FK;

Adding a New Foreign Key

ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DEPT_ID) 
REFERENCES DEPARTMENT (DEPT_ID) 
ON DELETE SET NULL;

• DROP FOREIGN KEY: Removes the existing DEPT_FK foreign key constraint.
• ADD FOREIGN KEY: Adds a new foreign key that sets DEPT_ID to NULL if the associated department is deleted.

Deleting a Foreign Key

To delete a foreign key constraint, use the ALTER TABLE statement with DROP FOREIGN KEY. This action removes the referential integrity link between the tables.

Syntax -

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

Examples - Deleting a Foreign Key

ALTER TABLE EMPLOYEE
DROP FOREIGN KEY DEPT_FK;

---