ＳＱＬ ＳＥＲＶＥＲ －－ＳＴＵＤＹ（10）

Anddy

转载：
http://www.51testing.com/?uid-182741-action-viewspace-itemid-95001
 
 
设置外键的同时：
 
ALTER TABLE PERSON ADD (CONSTRAINT FK_PERSON1 FOREIGN KEY (PROFESSION)REFERENCES PERSONPROFESSION (PROFESSIONID) ON DELETE CASCADE); 
 
增加外键约束的时候加ON DELETE CASCADE选项目的是可以在子表记录存在的情况下直接删除父表记录，而不用受约束的限制

在delete语句的末尾加上CASCADE，就可以实现两张表的级删除：
 
 
 
delete from PERSONPROFESSION cascade; 

 
 
 
 

 
 
 
 
 
 
以下是ON DELETE的官方解释：
ON DELETE
The ON DELETE clause indicates that when a DELETE is executed on a referenced row in the referenced table, one of the following actions will be executed upon the constrained column, as specified by action:
 
NO ACTION(default)
The NO ACTION clause produces an error if the reference is violated. This is the default if action is not specified.
CASCADE
The CASCADE keyword removes all rows which reference the deleted row. Exercise caution with this action.
SET NULL
The SET NULL clause assigns a NULL value to all referenced column values.
 
转载：
 
http://newsgroups.derkeiler.com/Archive/Comp/comp.databases.theory/2005-10/msg00137.html
 
ALTER TABLE foo ADD CONSTRAINT foo_bar_fk FOREIGN KEY (bar_id) REFERENCES
bar(bar_id) ON DELETE RESTRICT

- meaning: If someone tries to remove a row in bar which foo needs (for
referential integrity), the action is denied (in stead of cascaded as a
deletion in the foo table). See you DBMS's documentation for more on this.

RESTRICT can also be part of a command, like: DROP TABLE bar RESTRICT;

- meaning: Drop the "bar" table, unless the table is used as part of
referential integrity constraints in other tablesALTER TABLE foo ADD CONSTRAINT foo_bar_fk FOREIGN KEY (bar_id) REFERENCES
bar(bar_id) ON DELETE RESTRICT

- meaning: If someone tries to remove a row in bar which foo needs (for
referential integrity), the action is denied (in stead of cascaded as a
deletion in the foo table). See you DBMS's documentation for more on this.

RESTRICT can also be part of a command, like: DROP TABLE bar RESTRICT;

- meaning: Drop the "bar" table, unless the table is used as part of
referential integrity constraints in other tables
 
——————————————————————————————————————
 
http://www.w3schools.com/SQL/sql_alias.asp
 
SQL 语法网站