blocks|key|637197|text|您应该指定delete级联到employee表。这有点依赖于你的数据库，但是几乎每个数据库都支持级联删除。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|637198|例如在SQL+Server中：|637199|CREATE+TABLE+Employee
(
++++DepId+INT+NOT+NULL+REFERENCES+Department(DepId)+ON+DELETE+CASCADE
)|code-block|syntax|javascript|637200|entityMap^0|E|8|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

You should specify that the delete cascades to the <code>employee</code> table. Depends a little on your database how to do this but almost every database supports cascading deletes.

For example in SQL Server:

<pre><code>CREATE TABLE Employee
(
 DepId INT NOT NULL REFERENCES Department(DepId) ON DELETE CASCADE
)
</code></pre>

blocks|key|72371|text|这取决于数据库和您如何定义您的关系。您可以使删除级联，这意味着删除父级会删除子记录，或者“delete+nullify”，这会导致子关系在其FK字段中具有NULL，或者您拥有的内容。这取决于您的需求。通常，在没有自动处理的情况下，我不会根据需要定义作废、级联和删除每条记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|72372|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

It depends on the database and how you have defined your relationships. You could cause deletes to cascade which means that deleting a parent deletes the child records, or "deletes nullify," which causes the child relations to have NULLs in their FK fields, or what have you. It depends on your needs. Typically, I do not define NULLIFY or CASCADE and delete each record as needed without this being taken care of automatically.

blocks|key|72335|text|在SQL+Server中，您可以将关系标记为mark|type|unstyled|depth|inlineStyleRanges|entityRanges|data|72336|或者，首先对Employees表执行DELETE，然后对Departments表执行第二个DELETE。|72337|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

In SQL Server you would mark the relationship as CASCASE DELETE

Alternatively, execute a DELETE against the Employees table first, and then a second DELETE against the Departments table.

blocks|key|72411|text|在建立外键关系时，您可以定义(至少在MS+SQL+Server中)对更新和删除采取的操作。例如，您可以说“当部门中的记录被删除时，将删除级联到引用它的任何子表”。你可以找到更多信息here。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|72412|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms174979.aspx^0|2I|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

When you establish the foreign key relationship, you can define (in MS SQL Server, anyway) what actions are taken on update and delete. For instance, you can say "when a record in department is deleted, cascade the delete to any child tables that reference it". You can find more info <a href="http://msdn.microsoft.com/en-us/library/ms174979.aspx" rel="nofollow noreferrer">here</a>.

blocks|key|3852986|text|DB2语法(因为您尚未指定正在使用哪个数据库)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3852987|Create+table+Employee
...
...+
FOREIGN+KEY+(deptID)+REFERENCES+department+(deptID)
++++ON+DELETE+CASCADE|code-block|syntax|javascript|3852988|如果您指定了CASCADE+option，则删除部门时将删除员工。|3852989|如果不是CASCADE，而是指定RESTRICT，它将不允许您删除部门，除非首先删除属于该部门的所有员工。|3852990|或者，如果您不希望每次都发生这种情况，那么最好指定RESTRICT并首先在emp上执行delete。|3852991|Delete+*+from+Employee+where+deptID+=+10;
Delete+*+from+Dept+where+deptID+=+10;|3852992|这里有一个到examples的链接|offset|length|3852993|entityMap|0|LINK|mutability|MUTABLE|url|http://www.netcraft.com.au/geoffrey/postgresql/fkey.html^0|0|0|0|0|0|0|6|8|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|16|8|@]|9|@$Q|17|R|18|1|19]]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

DB2 syntax (since you have not specified which DB is being used):

<pre><code>Create table Employee
...
... 
FOREIGN KEY (deptID) REFERENCES department (deptID)
 ON DELETE CASCADE
</code></pre>

If you specify CASCADE option, employees will be deleted if the dept gets deleted.

If instead of CASCADE, you specify RESTRICT it won't allow you to delete dept unless all employees belonging to the dept are deleted first.

Alternately if you do not want this to happen everytime, then its better to specify RESTRICT and do a delete first on emp wherever reqd

<pre><code>Delete * from Employee where deptID = 10;
Delete * from Dept where deptID = 10;
</code></pre>

Here's a link to <a href="http://www.netcraft.com.au/geoffrey/postgresql/fkey.html" rel="nofollow noreferrer">examples</a>

How to delete a records in a table which has a foreign key relation?
Suppose I have a 2 tables: employee and department, the first hast a <code>deptID</code> as a foreign key referencing the department id.
Now I want to delete a specific row in a table like <code>delete from department where depID=10</code>. It is okay if the employees in the department are deleted, and I also want to maintain the foreign key relation. What is the query for doing this?

Deleting a record in a table which has foreign key relational tables

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何删除有外键关系的表中的记录？假设我有两个表: employee和department，第一个表有一个deptID作为引用部门id的外键。现在我想删除像delete from department where depID=10这样的表中的特定行。删除部门中的职员也可以，我也想维护外键关系。执行此操作的查询是什么？

问删除具有外键关系表的表中的记录
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问删除具有外键关系表的表中的记录EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问删除具有外键关系表的表中的记录
EN