blocks|key|532280|text|您需要定义一个外键：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|532281|ALTER+TABLE+Address
ADD+FOREIGN+KEY+(PersonId)
REFERENCES+Person+(PersonId)+|code-block|syntax|javascript|532282|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You need to define a foreign key:

<pre><code>ALTER TABLE Address
ADD FOREIGN KEY (PersonId)
REFERENCES Person (PersonId) 
</code></pre>

blocks|key|2705354|text|添加外键约束并限制删除：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2705355|ALTER+TABLE+Address
ADD+CONSTRAINT+Address_PersonId_fkey
FOREIGN+KEY+(PersonId)
REFERENCES+Person+(PersonId)
ON+DELETE+RESTRICT++--+what+you+asked+for
ON+UPDATE+CASCADE;++--+maybe+do+something+different+for+updates?|code-block|syntax|javascript|2705356|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Add a foreign key constraint and restrict deletes:

<pre><code>ALTER TABLE Address
ADD CONSTRAINT Address_PersonId_fkey
FOREIGN KEY (PersonId)
REFERENCES Person (PersonId)
ON DELETE RESTRICT -- what you asked for
ON UPDATE CASCADE; -- maybe do something different for updates?
</code></pre>

blocks|key|931170|text|你可以试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|931171|DELETE+Address+WHERE+PersonId+=+1|code-block|syntax|javascript|931172|在那之后|931173|DELETE+Person+WHERE+PersonId+=+1|931174|也可以在地址表中引用。|931175|ALTER+TABLE+Address
ADD+FOREIGN+KEY+(PersonId)
REFERENCES+Person+(PersonId)+|931176|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You can try this:

<pre><code>DELETE Address WHERE PersonId = 1
</code></pre>

after that 

<pre><code>DELETE Person WHERE PersonId = 1
</code></pre>

or you can reference in Address table

<pre><code>ALTER TABLE Address
ADD FOREIGN KEY (PersonId)
REFERENCES Person (PersonId) 
</code></pre>

blocks|key|2705259|text|PersonId+of+Person应该是PRIMARY+KEY，PersonId+of+Address表应该是FOREIGN+KEY，而FOREIGN+KEY应该是REFERENCE+PersonId+of+Person。如果这两个表之间存在这种关系，那么如果ID存在于Address表中，DBMS在从Person表中删除记录时将自动返回错误。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2705260|下面的SQL将更改您的表。|2705261|ALTER+TABLE+Persons
ADD+PRIMARY+KEY+(PersonId)

ALTER+TABLE+Address
ADD+FOREIGN+KEY+(PersonId)
REFERENCES+Persons(PersonId)|code-block|syntax|javascript|2705262|entityMap^0|0|8|C|6|L|B|X|8|19|7|1K|B|1X|B|2B|S|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|16|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|N|$]]

The <code>PersonId</code> of <code>Person</code> should be <code>PRIMARY KEY</code> and <code>PersonId</code> of <code>Address</code> table should be <code>FOREIGN KEY</code> which should <code>REFERENCE PersonId of Person</code>. If this relation exists between these two tables, then automatically the DBMS will return error on deleting a record from Person table if the ID exists in Address table. 

The below SQL will alter your tables.

<pre><code>ALTER TABLE Persons
ADD PRIMARY KEY (PersonId)

ALTER TABLE Address
ADD FOREIGN KEY (PersonId)
REFERENCES Persons(PersonId)
</code></pre>

blocks|key|932318|text|试试这个，这会解决你的案子|type|unstyled|depth|inlineStyleRanges|entityRanges|data|932319|SQL不应允许我从Person表中删除，因为Address表中有与该Person相关的行。|blockquote|932320|+DECLARE+@CNT+AS+BIGINT
++++SELECT+@CNT=ISNULL(COUNT(PersonId),0)+FROM+Address+where+PersonId=1+


++++IF+@CNT=0
++++++++BEGIN
++++++++++++DELETE+Person+WHERE+PersonId+=+1
++++++++END|code-block|syntax|javascript|932321|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|M|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|N|8|@]|9|@]|A|$H|I]]|$1|J|3|-4|5|6|7|O|8|@]|9|@]|A|$]]]|K|$]]

Try this,this will solve your case

<blockquote>
 SQL should not allow me to delete from Person table since there are
 rows related to that Person in Address table
</blockquote>

<pre><code> DECLARE @CNT AS BIGINT
 SELECT @CNT=ISNULL(COUNT(PersonId),0) FROM Address where PersonId=1 


 IF @CNT=0
 BEGIN
 DELETE Person WHERE PersonId = 1
 END
</code></pre>

blocks|key|931222|text|您可以在这些表中创建主键和外键关系，如果地址表中有相应的条目，这将防止从人员中删除记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|931223|CREATE+TABLE+Person
++++(
++++PersonId+INT+CONSTRAINT+pk_person_pid+PRIMARY+KEY,
++++Name+VARCHAR(25),
++++Surname+VARCHAR(25)
++++);


++++CREATE+TABLE+Address
++++(
++++AddressId+INT+CONSTRAINT+pk_address_aid+PRIMARY+KEY,
++++PersonId+INT+CONSTRAINT+fk_personAddress_pid+FOREIGN+KEY+REFERENCES+Person(PersonId),
++++AddressVARCHAR(25)
++++);|code-block|syntax|javascript|931224|参考:创建主键和外键约束|offset|length|931225|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.sqlauthority.com/2008/09/08/sql-server-%25E2%2580%2593-2008-creating-primary-key-foreign-key-and-default-constraint/^0|0|0|0|C|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

You can create primary key and foreign key relationships in these tables, this will prevent deleteing records from Person if there are corresponding entries in Address table. 

<pre><code>CREATE TABLE Person
 (
 PersonId INT CONSTRAINT pk_person_pid PRIMARY KEY,
 Name VARCHAR(25),
 Surname VARCHAR(25)
 );


 CREATE TABLE Address
 (
 AddressId INT CONSTRAINT pk_address_aid PRIMARY KEY,
 PersonId INT CONSTRAINT fk_personAddress_pid FOREIGN KEY REFERENCES Person(PersonId),
 AddressVARCHAR(25)
 );
</code></pre>

<a href="http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/" rel="nofollow">Reference: creating primary key, foreign key constraints</a>

<pre><code>Person Table
-------------------------------------
PersonId | Name | Surname |
-------------------------------------
1 John Smith
2 Sam Rainbow


Address Table
------------------------------------------
AddressId | PersonId | Address |
------------------------------------------
1 1 AAA
2 1 BBB
</code></pre>

so what I want to do is, If I execute the query below,

<pre><code>DELETE Person WHERE PersonId = 1
</code></pre>

SQL should not allow me to delete from Person table since there are rows related to that Person in Address table.

How can I create this relation?

Relation between tables

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

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

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

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

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

Person Table-------------------------------------PersonId   |   Name   |   Surname   |-------------------------------------1              John       Smith2              Sam        RainbowAddress Table

问表间关系
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问表间关系EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问表间关系
EN