Question

(a) When implementing a relational database schema in SQL, does it matter in which order you create tables? Hint: suppose that foreign keys exist.

(b) Explain the options available for enforcing a foreign key constraint when creating a foreign key, and what effects they have. What is the default option?

Answer 1

Answer A ) yes, when implementing a relational database schema in SQL, the order in which the tables are created matters. As also indicated in the hint. If a table A has a foreign_key associated with another table B, then we first need to create table B.

Read the following defiinition -

A foreign key is a key used to link two tables together. It is a field (or collection of fields) in one table that refers to the primary key in another table.

So if the Table B is missing than our table A won't find any key to refer to in table B.

Answer B)

There are on delete and on update options which can be used in conjunction with no action, cascade, set null and set default.

If we delete rows in the parent table, we can set one of the following actions:

• ON DELETE NO ACTION: rolls back the delete action on the row in the parent table.
• ON DELETE CASCADE: deletes the rows in the child table that is corresponding to the row deleted from the parent table.
• ON DELETE SET NULL: sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted, given that the foreign key columns must be nullable.
• ON DELETE SET DEFAULT: sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted, given that the foreign key columns must have default definitions.

Similarly for the UPDATE option -

If you update one or more rows in the parent table, you can set one of the following actions:

• ON UPDATE NO ACTION: rolls back the update action on the row in the parent table.
• ON UPDATE CASCADE: updates the corresponding rows in the child table when the rows in the parent table are updated.
• ON UPDATE SET NULL: sets the rows in the child table to NULL when the corresponding row in the parent table is updated, given that the foreign key columns must be nullable.
• ON UPDATE SET DEFAULT: sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.

By default, it is set to ON DELETE NO ACTION if we don’t explicitly specify any action.

Note - please leave a comment for clarification.