The following table shows how referential integrity can be used to enforce common business rules by defining relationships between the CUSTOMER, ORDERS, PRODUCTS, ITEMS, and EMPLOYEE tables in the TUTORIAL location.
| Business Rule | Database Relationships | |
|---|---|---|
| 1. | An order can be placed for a customer only if complete customer information is available. | Before inserting or updating into the ORDERS table, check for matching C_NO values in the CUSTOMER table. Do not delete CUSTOMER table record if orders for customer exist in the ORDERS table. |
| Business Rule | Database Relationships | |
|---|---|---|
| 1. | An order can be placed for a customer only if complete customer information is available. | Before inserting or updating into the ORDERS table, check for matching C_NO values in the CUSTOMER table. Do not delete CUSTOMER table record if orders for customer exist in the ORDERS table. |
| 2. | Orders must be taken by an employee of the company. If a salesperson for an order is unknown, the order is treated as a house order. | Before updating or inserting into the ORDERS table, check whether S_NO value has matching E_NO in the EMPLOYEE table. Before deleting EMPLOYEE table records, set S_NO of corresponding ORDERS table records to null. |
| Business Rule | Database Relationships | |
|---|---|---|
| 1. | An order can be placed for a customer only if complete customer information is available. | Before inserting or updating into the ORDERS table, check for matching C_NO values in the CUSTOMER table. Do not delete CUSTOMER table record if orders for customer exist in the ORDERS table. |
| 2. | Orders must be taken by an employee of the company. If a salesperson for an order is unknown, the order is treated as a house order. | Before updating or inserting into the ORDERS table, check whether S_NO value has matching E_NO in the EMPLOYEE table. Before deleting EMPLOYEE table records, set S_NO of corresponding ORDERS table records to null. |
| 3. | A request for an item must always be associated with a specific order. | Before inserting or updating the ITEMS table, check whether O_NO columns have matching O_NO values in the ORDERS table. If ORDERS record is deleted, delete matching ITEMS table records. |
| Business Rule | Database Relationships | |
|---|---|---|
| 1. | An order can be placed for a customer only if complete customer information is available. | Before inserting or updating into the ORDERS table, check for matching C_NO values in the CUSTOMER table. Do not delete CUSTOMER table record if orders for customer exist in the ORDERS table. |
| 2. | Orders must be taken by an employee of the company. If a salesperson for an order is unknown, the order is treated as a house order. | Before updating or inserting into the ORDERS table, check whether S_NO value has matching E_NO in the EMPLOYEE table. Before deleting EMPLOYEE table records, set S_NO of corresponding ORDERS table records to null. |
| 3. | A request for an item must always be associated with a specific order. | Before inserting or updating the ITEMS table, check whether O_NO columns have matching O_NO values in the ORDERS table. If ORDERS record is deleted, delete matching ITEMS table records. |
| 4. | Only items that are carried can be requested on an order. | Before inserting or updating records in ITEMS, make sure P_NO matches a P_NO in the PRODUCTS table. Prevent deletion of a PRODUCTS table record if dependent ITEMS table records exist. |
| Primary key: | customer(c_no) |
| Foreign key: | orders(o_no) |
| Delete rule: | RESTRICT |
| Primary key: | employee(e_no) |
| Foreign key: | orders(s_no) |
| Delete rule: | SET NULL |
| Primary key: | orders(o_no) |
| Foreign key: | items(o_no) |
| Delete rule: | CASCADE |
| Primary key: | products(p_no) |
| Foreign key: | items(p_no) |
| Delete rule: | RESTRICT |
| 2. | Orders must be taken by an employee of the company. If a salesperson for an order is unknown, the order is treated as a house order. | Before updating or inserting into the ORDERS table, check whether S_NO value has matching E_NO in the EMPLOYEE table. Before deleting EMPLOYEE table records, set S_NO of corresponding ORDERS table records to null. |
| 3. | A request for an item must always be associated with a specific order. | Before inserting or updating the ITEMS table, check whether O_NO columns have matching O_NO values in the ORDERS table. If ORDERS record is deleted, delete matching ITEMS table records. |
| 4. | Only items that are carried can be requested on an order. | Before inserting or updating records in ITEMS, make sure P_NO matches a P_NO in the PRODUCTS table. Prevent deletion of a PRODUCTS table record if dependent ITEMS table records exist. |
| 3. | A request for an item must always be associated with a specific order. | Before inserting or updating the ITEMS table, check whether O_NO columns have matching O_NO values in the ORDERS table. If ORDERS record is deleted, delete matching ITEMS table records. |
| 4. | Only items that are carried can be requested on an order. | Before inserting or updating records in ITEMS, make sure P_NO matches a P_NO in the PRODUCTS table. Prevent deletion of a PRODUCTS table record if dependent ITEMS table records exist. |
| 4. | Only items that are carried can be requested on an order. | Before inserting or updating records in ITEMS, make sure P_NO matches a P_NO in the PRODUCTS table. Prevent deletion of a PRODUCTS table record if dependent ITEMS table records exist. |
All of the tables named already exist in the TUTORIAL location. However, if the tables did not already exist (or if you create them in another location), you could specify the referential integrity constraints at the same time you created the table.
Note that the order in which the commands are executed is important. You cannot create a table that references another table until the referenced table is created. For example, the CUSTOMER table must be created before the ORDERS table.