SQL 删除作为另一个表的外键的主键行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8982172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
delete primary key row which is foreign key of another table
提问by aravind
Suppose there is a main table containing a primary key and there is another table which contains a foreign key to this main table. So if we delete the row of main table only not child table.
假设有一个包含主键的主表，还有另一个包含该主表的外键的表。所以如果我们只删除主表的行而不删除子表。
回答by Oleg Dok
You should set some specific options on your FKey, such as ON DELETE {CASCADE, SET NULL, SET DEFAULT}
您应该在 FKey 上设置一些特定选项，例如 ON DELETE {CASCADE, SET NULL, SET DEFAULT}
Instead you'll not be able to delete referenced row, because it is prohibited by sql server due to referrential integrity.
相反，您将无法删除引用的行，因为由于引用完整性，它被 sql server 禁止。
So, the option is to set referencing table's value to NULL or any other DEFAULT value.
因此，选项是将引用表的值设置为 NULL 或任何其他 DEFAULT 值。
Ordelete it too
或者也删除
Or, if your referencing row has some meaning without parent row - then something is wrong with your DB design- either you do not need the FKey or schema is not normalized.
或者，如果您的引用行在没有父行的情况下具有某种含义 - 那么您的数据库设计有问题- 您不需要 FKey 或架构未规范化。
回答by Rachana
Try something like this
尝试这样的事情
ALTER TABLE MainTable
ADD CONSTRAINT fk_xyz
FOREIGN KEY (xyz)
REFERENCES ChildTable (xyz) ON DELETE CASCADE
回答by Shree
i think u want some thing like this.
我想你想要这样的东西。
ON DELETE CASCADE Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.
ON DELETE CASCADE 指定如果尝试删除具有由其他表中现有行中的外键引用的键的行，则包含这些外键的所有行也将被删除。
ref:http://msdn.microsoft.com/en-us/library/ms186973%28SQL.90%29.aspx
参考：http: //msdn.microsoft.com/en-us/library/ms186973%28SQL.90%29.aspx
