Referential integrity: FOREIGN KEY page 2
Now it’s 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 doesn’t have a corresponding record in the parent table, to the child table. In our case, respective foreign keys won’t allow adding a product to any of the descriptive tables (PC, Laptop, or Printer) whose model isn’t present in the Product table. For instance, the attempt to execute the statement
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 isn’t present in the corresponding column of the parent table. In our database, the constraint won’t allow the following UPDATE statement, returning a similar error:
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
Following option values are possible:
Since we haven’t specified any ON DELETE clause when creating the foreign key for the PC table, NO ACTION will be used, because it’s 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: