Data Definition Language (SQL DDL)

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

INSERT INTO pc VALUES(13, 1126, 500, 64, 10, '24x', 650);

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:

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

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 won’t 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 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:
- delete the existing constraint;
- create a new constraint.