Referential integrity: FOREIGN KEY page 2

Now its time to gain an understanding how the foreign key constraint works.

Since this constraint ensures the consistency of data in two tables, it prevents the appearance of rows in the child table for which no corresponding rows exist in the parent table. Discordance could arise as a result of one of the following actions:

1.    Adding a row that doesnt have a corresponding record in the parent table, to the child table. In our case, respective foreign keys wont allow adding a product to any of the descriptive tables (PC, Laptop, or Printer) whose model isnt present in the Product table. For instance, the attempt to execute the statement

  1. INSERT INTO pc VALUES(13, 1126, 500, 64, 10, '24x', 650);
will produce an error, since the model 1126 doesnt exist in the Product table:

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_pc_product". The conflict occurred in database "learn", table "dbo.product", column 'model'. The statement has been terminated.

2.    Setting the foreign key attribute of an existing record to a value that isnt present in the corresponding column of the parent table. In our database, the constraint wont allow the following UPDATE statement, returning a similar error:

  1. UPDATE pc SET model = 1126 WHERE model = 1121;

3.    Deleting a row in the parent table, for which associated records exist in the child table. Here, data consistency can be ensured in different ways, as specified by option in the non-compulsory clause

  1. ON DELETE <>

Following option values are possible:

  • CASCADE  -  cascade deletion, that is, when a row is deleted from the parent table all corresponding rows from the child table will also be deleted. For instance, on deletion of model 1121 from the Product table, the rows with codes 2, 4, and 5 will be deleted from the PC table;
  • SET NULL   - on deletion of a row from the parent table the foreign key attribute of the corresponding rows in the child table becomes indeterminate. Obviously, this setting implies there is no NOT NULL constraint applied to the foreign key. In our example of deleting model 1121 from the Product table, the model column in the PC table will be set to NULL for the rows with codes 2, 4, and 5;
  • SET DEFAULT   similar to the previous setting, but instead of NULL, the foreign key is set to the default value;
  • NO ACTION (default setting) the statement wont be executed if there are corresponding rows in the child table for the record to be deleted. If there are no corresponding rows, the deletion will be carried out normally.

Since we havent specified any ON DELETE clause when creating the foreign key for the PC table, NO ACTION will be used, because its the default option. To change the database behavior to, say, cascade deletion, we have to revise the foreign key constraint. We can do it as follows:
- delete the existing constraint;
- create a new constraint.

Bookmark and Share
Pages 1 2 3
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100