sql_foreignkey

SQL FOREIGN KEY Constraint: Ensuring Relational Integrity

Understanding FOREIGN KEY Constraints

A FOREIGN KEY is a column (or a set of columns) in one table that references the PRIMARY KEY (or a UNIQUE key) in another table. This establishes a link between the two tables, enforcing referential integrity.

Let’s illustrate with an example. Consider two tables: “Persons” and “Orders”.

The “Persons” table (Parent Table):

PersonID (PK)
LastName
FirstName
Address
City


1
Hansen
Ola
Timoteivn 10
Sandnes


2
Svendson
Tove
Borgvn 23
Sandnes


3
Pettersen
Kari
Storgt 20
Stavanger

The “Orders” table (Child Table):

OrderID (PK)
OrderNumber
PersonID (FK)
OrderDate


101
77895
3
2023-01-15


102
44678
3
2023-01-18


103
22456
2
2023-02-01


104
24562
1
2023-02-05

In this example, the PersonID column in the “Orders” table is a FOREIGN KEY that references the PersonID (Primary Key) in the “Persons” table.

Benefits of Foreign Keys:

• Data Integrity: Prevents actions that would create orphaned records (e.g., an order for a non-existent person).
• Referential Consistency: Ensures that values in the foreign key column match existing values in the referenced primary key column.
• Data Relationships: Clearly defines relationships between tables, which is crucial for database design and complex queries.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL shows how to create a FOREIGN KEY on the PersonID column when the “Orders” table is created:

General Syntax (widely supported):

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderNumber INT NOT NULL,
  PersonID INT,
  OrderDate DATE,
  CONSTRAINT FK_Order_Person FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
  -- Optional: ON DELETE and ON UPDATE actions
  -- ON DELETE SET NULL
  -- ON DELETE CASCADE
  -- ON UPDATE CASCADE
);

Note: Naming the constraint (e.g., FK_Order_Person) is a good practice, making it easier to manage later. Some databases allow inline foreign key definitions for single columns:

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderNumber INT NOT NULL,
  PersonID INT REFERENCES Persons(PersonID), -- Inline syntax
  OrderDate DATE
);

Referential Actions (ON DELETE, ON UPDATE):

• RESTRICT or NO ACTION (default): Prevents deleting/updating a parent row if child rows exist.
• CASCADE: Deleting/updating a parent row also deletes/updates corresponding child rows.
• SET NULL: Deleting/updating a parent row sets the foreign key column(s) in child rows to NULL (requires the FK column to be nullable).
• SET DEFAULT: Sets the foreign key to its default value (less common).

SQL FOREIGN KEY Constraint on ALTER TABLE

To add a FOREIGN KEY constraint to an existing “Orders” table:

General Syntax:

ALTER TABLE Orders
ADD CONSTRAINT FK_Order_Person
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To DROP a FOREIGN KEY Constraint

To remove an existing FOREIGN KEY constraint:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_Order_Person;

SQL Server, Oracle, PostgreSQL, and others (Standard SQL):

ALTER TABLE Orders
DROP CONSTRAINT FK_Order_Person;

Practical Application: Foreign keys are fundamental to relational database design. They model real-world relationships (e.g., a customer has many orders, an employee belongs to a department) and ensure data consistency across the database.