FOREIGN KEY CONSTRAINTS

  • A FOREIGN  KEY uses a primary key in another table or a unique constraint from another table to find the relationship between the columns.
  • A FOREIGN KEY can be null

 

What happens when the primary or Unique key changes?

  1. no action: no action will be taken. Nothing is committed.
  2. cascade: changes in the table will result in changes to related tables
  3. set null: the related tables are set to NULL

 

Delete a Table with CASCADE

ALTER TABLE TBL_TRANSACTION
ADD CONSTRAINT FK_TBL_TRANSACTION_EMPLOYEE_NUMBER
FOREIGN KEY (EMPLOYEE_NUMBER)
REFERENCES TBL_EMPLOYEE(EMPLOYEE_NUMBER)
ON DELETE CASCADE;

Code Explanation
ALTER TABLE TBL_TRANSACTION: This specifies that the subsequent alterations apply to the TBL_TRANSACTION table.

ADD CONSTRAINT FK_TBL_TRANSACTION_EMPLOYEE_NUMBER: This line is adding a new constraint to the table, and it’s named FK_TBL_TRANSACTION_EMPLOYEE_NUMBER. Constraints are rules that enforce data integrity.

FOREIGN KEY (EMPLOYEE_NUMBER): This indicates that the constraint being added is a foreign key constraint, and it is applied to the EMPLOYEE_NUMBER column in the TBL_TRANSACTION table.

REFERENCES TBL_EMPLOYEE(EMPLOYEE_NUMBER): This specifies the referenced table and column for the foreign key. In this case, the EMPLOYEE_NUMBER column in TBL_TRANSACTION is a foreign key that references the EMPLOYEE_NUMBER column in the TBL_EMPLOYEE table.

ON DELETE CASCADE: This part of the statement specifies the action to be taken when a referenced row in TBL_EMPLOYEE is deleted. In this case, it’s set to CASCADE, meaning that if a row in TBL_EMPLOYEE with a matching EMPLOYEE_NUMBER is deleted, all corresponding rows in TBL_TRANSACTION with that EMPLOYEE_NUMBER will also be automatically deleted. This ensures referential integrity by removing related data in the dependent table when a referenced row is deleted.

 

Alter table to set Matching Values to NULL

ALTER TABLE TBL_TRANSACTION
ADD CONSTRAINT FK_TBL_TRANSACTION_EMPLOYEE_NUMBER
FOREIGN KEY (EMPLOYEE_NUMBER)
REFERENCES TBL_EMPLOYEE(EMPLOYEE_NUMBER)
ON DELETE SET NULL
ENABLE NOVALIDATE;

 

Explanation:

ON DELETE SET NULL: This part of the statement specifies the action to be taken when a referenced row in TBL_EMPLOYEE is deleted. In this case, it’s set to SET NULL, meaning that if a row in TBL_EMPLOYEE with a matching EMPLOYEE_NUMBER is deleted, the corresponding EMPLOYEE_NUMBER in TBL_TRANSACTION will be set to NULL. This is a way to handle referential integrity by nullifying the foreign key in the dependent table when a referenced row is deleted.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top