ASA Getting Started
Updating the Database
Integrity checking
The following statement inserts a new row in the sales_order table, but incorrectly supplies a sales_rep ID that does not exist in the employee table.
INSERT INTO sales_order ( id, cust_id, order_date, sales_rep) VALUES ( 2700, 186, '1995-10-19', 284 )
There is a one-to-many relationship between the employee table and the sales_order table, with a join between the sales_rep field of the sales_order table and the emp_id field of the employee table. Only after a record in the table containing the primary key for the join (the employee table) has been entered can a corresponding record on table containing the foreign key (the sales_order table) be inserted.
The primary key for the employee table is the employee ID number. The sales rep ID number in the sales_rep table is a foreign key for the employee table, meaning that each sales rep number in the sales_order table must match the employee ID number for some employee in the employee table.
When you try to add an order for sales rep 284 you get an error message:
No primary key value for foreign key 'ky_so_employee_id' in table 'sales_order'
There isn't an employee in the employee table with that ID number. This prevents you from inserting orders without a valid sales rep ID. This kind of validity checking is called referential integrity checking as it maintains the integrity of references among the tables in the database.
For more information on primary and foreign keys, see Relations between tables.