|
The following statement inserts a new row in the SalesOrders table, but incorrectly supplies a SalesRepresentative ID that does not exist in the Employees table.
INSERT INTO SalesOrders ( ID, CustomerID, OrderDate, SalesRepresentative) VALUES ( 2700, 186, '2000-10-19', 284 );
There is a one-to-many relationship between the Employees table and the SalesOrders table, based on the SalesRepresentative column of the SalesOrders table and the EmployeeID column of the Employees table. Only after a record in the primary table (Employees) has been entered can a corresponding record in the foreign table (SalesOrders) be inserted.
The primary key for the Employees table is the employee ID number. The sales rep ID number in the SalesRepresentative table is a foreign key for the Employees table, meaning that each sales rep number in the SalesOrders table must match the employee ID number for some employee in the Employees table.
When you try to add an order for sales rep 284 you get an error message:
No primary key value for foreign key 'FK_SalesRepresentative_EmployeeID' in table 'SalesOrders'
There isn't an employee in the Employees 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 checks the integrity of references among the tables in the database.
For more information on primary and foreign keys, see Relations between tables.