Using an example, explain how referential integrity is established during the creation of database
tables?

Question

Using an example, explain how referential integrity is established during the creation of database
tables?

Accepted Answer

⚹ Referential integrity is a concept in the relational database model that ensures relationships between tables remain consistent ⚹ ⚹ It is typically enforced through the use of foreign keys ⚹ ⚹ Let's consider an example with two tables: `Customers` and `Orders` ⚹ ⚹ The `Customers` table has a primary key called `CustomerID` ⚹ ⚹ The `Orders` table includes a column called `CustomerID` which will be the foreign key ⚹ ⚹ To establish referential integrity, the foreign key `CustomerID` in the `Orders` table must match a valid `CustomerID` in the `Customers` table ⚹ ⚹ When creating the `Orders` table, the foreign key constraint is defined to reference the `CustomerID` of the `Customers` table ⚹ ⚹ Here is an example SQL statement to create the `Orders` table with referential integrity: ⚹ ```sql CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, CustomerID int, OrderDate date, PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); ``` ⚹ This SQL statement creates a foreign key on the `CustomerID` column of the `Orders` table that references the `CustomerID` column of the `Customers` table ⚹ ⚹ If an attempt is made to insert an order with a `CustomerID` that does not exist in the `Customers` table, the database system will reject the insert operation to maintain referential integrity ⚹ ⚹ Similarly, if there is an attempt to delete a customer from the `Customers` table who has orders in the `Orders` table, the delete operation will be rejected unless those orders are also deleted or the foreign key is set to null, depending on the foreign key constraints ⚹ ⚹ Referential integrity ensures that the data across these related tables remains consistent and accurate ⚹

Sia