Data Definition Language (SQL DDL)

For constraint removal, the ALTER TABLE statement is used, as well:

ALTER TABLE < TABLE name > 
DROP CONSTRAINT < constraint name >;

That’s where the constraint name becomes necessary! Let’s remove the foreign key from the PC table.

ALTER TABLE PC 
DROP CONSTRAINT fk_pc_product;

Now, we create a new constraint making use of cascade delete:

ALTER TABLE PC
ADD CONSTRAINT fk_pc_product
FOREIGN KEY(model) REFERENCES Product ON DELETE CASCADE;

4.    Changes of column values in the parent table the foreign key in the child table refers to, that is, of columns listed in the REFERENCE clause of the FOREIGN KEY constraint. Here, the same behavior patterns are possible as when a row is deleted, but option is introduced by the clause 

ON UPDATE < OPTION >

By means of the foreign key, as well as of other constraints, we model connections existing in the subject area. Therefore, the choice of the constraint behavior options is stipulated exactly by the subject area. In our case, it seems natural to create a constraint with CASCADE for changing/deleting the model number in the Product table, so that such a change propagates to the descriptive tables, say, deleting articles of the void model. In other words, for the PC table, we should define the foreign key constraint as follows: 

ALTER TABLE PC
ADD CONSTRAINT fk_pc_product
FOREIGN KEY(model) REFERENCES Product 
            ON DELETE CASCADE
            ON UPDATE CASCADE;

However, for a different subject area cascade deletion may result in data loss. Say, we have an Employees and a Departments table linked together by the department number, and on deletion (breaking-up) of a department employees working there aren’t dismissed but transferred to other departments. In this case, cascade deletion would lead to an undesired removal of information on employees who have been working in the division broken up. Here, NO ACTION (first assign employees to other divisions, then delete the “emptied” department) or SET NULL (first delete the department, then find new jobs for employees not assigned to any department) seems more appropriate. I’d like to repeat one more time – the choice of the constraint behavior depends not on the programmer’s preferences but on processes taking place in the real world being modeled.

Notes

1.    In the example above, we established a one-to-many relationship between the Product and PC tables. A one-to-one relationship is created when a unique column or column combination represents the foreign key for the child table. In a number of cases, such a relationship can be attributed to flawed database design, since an entity is practically divided in two. However, in some situations there are forcible reasons for such a separation – for instance, if a vertical table partitioning needs to be done to improve performance or because of security considerations.

2.    To delete a constraint, one needs to know its name. However, as we’ve already seen, a constraint can be created without being explicitly assigned a name. What is to be done in such a situation? Well, if we don’t explicitly specify a name it’s generated by the system. Thus, the constraint always has a name – whether we know it, is another question. This is an appropriate point to say that relational databases store metadata exactly the same way as “ordinary” data – namely, in tables. By standard, metadata is represented by an information schema, and can be retrieved from it by an ordinary SELECT query. Without going into detail, let’s write a query getting us the foreign key constraint name for the PC table:

SELECT CONSTRAINT_NAME  
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME ='PC' AND CONSTRAINT_TYPE ='FOREIGN KEY';