Due date: Sunday 12th March
A referential integrity constraint ensures that relationships between tables remain consistent. It states that you may not add a record to the table unless the foreign key value matches a value of the key of the referenced table or is NULL.
FOREIGN KEY clause can be used to enforce referential integrity; it specifies the referenced table and key.
The FOREIGN KEY clause can also contain sub-clauses for referential actions (ON UPDATE and ON DELETE) when a value of the key is used in a foreign key and is
updated, or its record is deleted. These two sub-clauses may specify the following options:
NO ACTION: Changes (updates or deletes) to the primary key values in the referenced table that may violate referential integrity are not performed; and a message is displayed. This is the default.CASCADE: Changes to the primary key values in the referenced table are automatically propagated to the foreign key values in the referencing table.SET NULL: the foreign key values in the referencing table are set to NULL when their corresponding values are changed (or deleted) in the referenced table.SET DEFAULT: that sets a default value rather than NULL.RESTRICT: mostly the same as NO ACTION.1) Run the DB Script:
2) Try to enter products with a vendor code that does not appear in the Vendor table, for example: 99999. What happens? Are there any messages displayed by the system?
3) The store just received a delivery of the product ‘Sledge hammer, 6kg’ whose code is 23114-AA, increasing the total count in inventory by 100 pieces. So let’s update the product record to reflect this increase in quantity on hand. Show your query and show the affected table.
4) Try to update the vendor code of vendor ‘Bryson, Inc.’ in the Vendor table. What happens to the table Product (those products supplied by that vendor)? Why?