Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

外键 删除

Posted on 2010-05-09 18:15 Prayer 阅读(194) 评论(0)  编辑 收藏 引用 所属分类: DB2
DELETE Rules
When you delete a row from a parent table, the database manager checks if there
are any dependent rows in the dependent table with matching foreign key values. If
any dependent rows are found, several actions can be taken. You determine which
action will be taken by specifying a delete rule when you create the dependent
table.

RESTRICT — This rule prevents any row in the parent table from being deleted if
any dependent rows are found. If you need to remove both parent and dependent
rows, delete dependent rows first.
• NO ACTION — This rule enforces the presence of a parent row for every child
after all the referential constraints are applied. This is the default. The difference
between NO ACTION and RESTRICT is based on when the constraint is enforced.
See the DB2 SQL Reference for further details.
• CASCADE DELETE — This rule implies that deleting a row in the parent table
automatically deletes any related rows in the dependent table.
• SET NULL — This rule ensures that deletion of a row in the parent table sets the
values of the foreign key in any dependent row to null (if nullable). Other parts
of the row are unchanged.


UPDATE Rules
The database manager prevents the update of a unique key of a parent row. When
you update a foreign key in a dependent table and the foreign key is defined with
NOT NULL option, it must match some value of the parent key of the parent table.
Two options exist:
• RESTRICT — The update for the parent key will be rejected if a row in the
dependent table matches the original values of the key.
• NO ACTION — The update operation for the parent key will be rejected if any row
in the dependent table does not have a corresponding parent key when the
update statement is completed (excluding after triggers). This is the default


在更新父表中的键值时,可以指定两条规则:RESTRICT 和 NO ACTION。如果从属表中有从属行,则 RESTRICT 不允许更新键值。如果在更新完成时在从属表中有从属行,而从属行在父表中没有父键,则 NO ACTION 将导致对父键值的更新操作被拒绝

RESTRICT 是在更新之前限制,
NO ACTION 是在更新之后限制,
结果一样,判断的时间点不同

简单地说 On delete no action 和 ON DELETE RESTRICT 的区别不大,ON DELETE RESTRICT 要比 On delete no action 的检查条件严格些
书上抄来的解释(注意,解释几乎完全相同,唯一区别在于检查键关联的时间一个是 after,一个是 before):

ON DELETE NO ACTION. This definition ensures that whenever a delete operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have a matching value in the parent key of the parent table (after all other referential constraints have been applied).


ON DELETE RESTRICT. This definition ensures that whenever a delete operation is performed on the parent table of a referential constraint, the value for the foreign key of each row in the child table will have a matching value in the parent key of the parent table (before any other referential constraints are applied).


只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理