SQL Anywhere performs integrity checks when executing INSERT statements. For example, suppose you attempt to create a department, but supply a DepartmentID value that is already in use:
INSERT INTO Departments ( DepartmentID, DepartmentName, DepartmentHeadID ) VALUES ( 200, 'Eastern Sales', 902 );
The INSERT is rejected because the primary key for the table would no longer be unique. Since the DepartmentID column is a primary key, duplicate values are not permitted.
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 similar to the following:
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 representative ID.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|