Foreign Keys and Referential Integrity
In SQL database management systems, a foreign key is a column or group of columns in a database user-created table. Foreign keys are typically used to create links between data in two database tables. A foreign key created in one database table directly points to and links to a primary key or unique key defined in another table. This creates a direct or indirect link between the two database tables, establishing a relationship between them. This concept is crucial for referential integrity in database management systems, ensuring that the relationships between database tables are consistently maintained.
Let’s now understand the concept of foreign keys and how to apply referential integrity in a database.
Foreign Keys in Database Management Systems.
In SQL database management systems, a foreign key is a set of columns or a group of columns in a database table. Foreign keys are used in database tables to create and apply links between data in two different database tables. The created foreign key in one table properly matches the primary key or unique key in another table. The foreign key in the database table ensures that the column value in one table exactly matches the value in the other table, or is defined as a NULL value, thereby creating a relationship between the two different tables.
Features of Foreign Keys in DBMS.
- Referential Integrity – In SQL database management systems, a foreign key ensures that the value in the foreign key table column matches the corresponding value in the referenced table.
- Enforcing Relationships – This feature establishes relationships between two tables. For example, the `customer_id` in the Orders database table can be a foreign key that references the `customer_id` in the Customers table.
- NULL Values - If the relationship in the database table is optional, then NULL values can be defined in the foreign key column in the table. This usually happens when a row in the child table doesn’t necessarily have a proper match with a row in the parent table in two different database tables.
- Cascading Actions – In database tables, foreign keys are properly configured or set to automatically trigger equal actions on related table row data in the child table when any type of modification is triggered in the parent table, such as deleting table data or updating or inserting existing data.
Syntax for creating a foreign key in SQL.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
customer_name VARCHAR(100);
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
In this example.
- Here, the customer_id column in the orders table is defined with a foreign key, which refers to the customer_id in the existing customers table.
- The foreign key ensures that every customer_id in the orders table exactly matches a proper customer_id in the customers table.
Referential Integrity in Database Management Systems.
Referential integrity is a popular concept in database management systems that ensures that the relationships between the created database tables are created and maintained in the proper order. This feature assures the database user that the foreign key value in the created child table will always properly match the primary key value in the parent table or be defined as NULL. This is applied when referential integrity is implemented in database tables, allowing the database user to block or control such situations.
Elements of Referential Integrity in Database Management Systems.
- Orphaned records – These are child table records in the database table that refer to parent records in the table that no longer exist.
- Inconsistent data – These are data integrity problems in the database table that violate the foreign key relationship in the database table.
How Referential Integrity works in DBMS. In a database management system, the primary key in a parent table is defined by the database creator and is referenced or indicated by the linked child table. A foreign key constraint in the database table ensures that the foreign key value in the child database table either properly matches a primary key value in the parent table or is defined as NULL if the relationship is optional.
For example, consider this applied to two different tables.
- Customers table (Parent table) – the main table.
- Orders table (Child table) – linked with the parent table.
Here, in the Orders database table, `customer_id` is defined as a foreign key, which references or indicates the `customer_id` in the Customers database table.
Without referential integrity in the database table, a database user could have an orders record with a `customer_id` that does not exist in the Customers table, resulting in invalid or inconsistent data. Applying the referential integrity concept prevents such problems from occurring.
A complete example of a foreign key with parent and child tables.
Let’s understand foreign keys and referential integrity better through an example.
Parent table creation steps: Customers table.
CREATE TABLE customer (
customer_id INT PRIMARY KEY,
cust_name VARCHAR(140),
);
Child table creation steps: Orders table.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_name VARCHAR(130),
order_date DATE,
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Now insert manual data into the Customers table.
INSERT INTO customer (customer_id, cust_name)
VALUES (109, ‘Harry Deora’), (401, ‘Siddhi Deora’);
Insert data manually into the Orders child table.
INSERT INTO orders (order_id, order_name, customer_id, order_date)
VALUES (202, ‘Hp Laptop ‘, 109, ‘2026-01-08’), (444, ‘Dell Laptop’, 401, ‘2026-02-07’);
Here, deleting a record from the Customers table will automatically delete its corresponding orders.
DELETE FROM customer WHERE customer_id = 109; — This will also delete all orders for the customer named Harry in the customer table.
In this example.
- the Customers table is defined as the parent table, and the Orders table is defined as the child table.
- In the Orders table, `customer_id` is created as a foreign key, which references the `customer_id` in the Customers table.
- The `ON DELETE CASCADE` statement ensures that if a customer is deleted from the Customers table, all corresponding customer orders in the Orders table will also be automatically deleted.
- The `ON UPDATE CASCADE` statement ensures that if the `customer_id` is updated in the Customers table, this information will be automatically updated in the Orders table.
