loading..
   English
21:18

Referential integrity: FOREIGN KEY page 3

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

  1. ALTER TABLE < TABLE name >
  2. DROP CONSTRAINT < constraint name >;

Thats where the constraint name becomes necessary! Lets remove the foreign key from the PC table.

  1. ALTER TABLE PC
  2. DROP CONSTRAINT fk_pc_product;

Notes:

When the foreign key is removed, the columns it is applied to are not deleted, just the constraint itself is dropped. This holds true for other constraints, as well.

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

  1. ALTER TABLE PC
  2. ADD CONSTRAINT fk_pc_product
  3. 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 

  1. 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: 

  1. ALTER TABLE PC
  2. ADD CONSTRAINT fk_pc_product
  3. FOREIGN KEY(model) REFERENCES Product
  4. ON DELETE CASCADE
  5. 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 arent 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. Id like to repeat one more time the choice of the constraint behavior depends not on the programmers 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 weve 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 dont explicitly specify a name its 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, lets write a query getting us the foreign key constraint name for the PC table:

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

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100