blocks|key|791970|text|以下是这些关系类型的一些实际示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|791971|一对一(1:1)|791972|当且仅当表A中的一条记录与表B中的最多一条记录相关时，关系才是一对一的。|791973|要建立一对一的关系，表B的主键(没有孤立记录)必须是表A的次键(有孤立记录)。|791974|例如：|791975|CREATE+TABLE+Gov(
++++GID+number(6)+PRIMARY+KEY,+
++++Name+varchar2(25),+
++++Address+varchar2(30),+
++++TermBegin+date,
++++TermEnd+date
);+

CREATE+TABLE+State(
++++SID+number(3)+PRIMARY+KEY,
++++StateName+varchar2(15),
++++Population+number(10),
++++SGID+Number(4)+REFERENCES+Gov(GID),+
++++CONSTRAINT+GOV_SDID+UNIQUE+(SGID)
);

INSERT+INTO+gov(GID,+Name,+Address,+TermBegin)+
values(110,+'Bob',+'123+Any+St',+'1-Jan-2009');

INSERT+INTO+STATE+values(111,+'Virginia',+2000000,+110);|code-block|syntax|javascript|791976|一对多(1:M)|791977|当且仅当表A中的一条记录与表B中的一条或多条记录相关时，关系才是一对多关系。但是，表B中的一条记录不能与表A中的多条记录相关。|791978|要建立一对多关系，表A(“一”表)的主键必须是表B(“多”表)的次键。|791979|791980|CREATE+TABLE+Vendor(
++++VendorNumber+number(4)+PRIMARY+KEY,
++++Name+varchar2(20),
++++Address+varchar2(20),
++++City+varchar2(15),
++++Street+varchar2(2),
++++ZipCode+varchar2(10),
++++Contact+varchar2(16),
++++PhoneNumber+varchar2(12),
++++Status+varchar2(8),
++++StampDate+date
);

CREATE+TABLE+Inventory(
++++Item+varchar2(6)+PRIMARY+KEY,
++++Description+varchar2(30),
++++CurrentQuantity+number(4)+NOT+NULL,
++++VendorNumber+number(2)+REFERENCES+Vendor(VendorNumber),
++++ReorderQuantity+number(3)+NOT+NULL
);|791981|多对多(M:M)|791982|当且仅当表A中的一条记录与表B中的一条或多条记录相关时，关系才是多对多关系，反之亦然。|791983|要建立多对多关系，请创建名为"ClassStudentRelation“的第三个表，该表将同时具有表A和表B的主键。|791984|CREATE+TABLE+Class(
++++ClassID+varchar2(10)+PRIMARY+KEY,+
++++Title+varchar2(30),
++++Instructor+varchar2(30),+
++++Day+varchar2(15),+
++++Time+varchar2(10)
);

CREATE+TABLE+Student(
++++StudentID+varchar2(15)+PRIMARY+KEY,+
++++Name+varchar2(35),
++++Major+varchar2(35),+
++++ClassYear+varchar2(10),+
++++Status+varchar2(10)
);++

CREATE+TABLE+ClassStudentRelation(
++++StudentID+varchar2(15)+NOT+NULL,
++++ClassID+varchar2(14)+NOT+NULL,
++++FOREIGN+KEY+(StudentID)+REFERENCES+Student(StudentID),+
++++FOREIGN+KEY+(ClassID)+REFERENCES+Class(ClassID),
++++UNIQUE+(StudentID,+ClassID)
);|791985|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|18|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|19|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1A|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1B|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1C|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1E|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1F|8|@]|9|@]|A|$]]|$1|U|3|I|5|6|7|1G|8|@]|9|@]|A|$]]|$1|V|3|W|5|L|7|1H|8|@]|9|@]|A|$M|N]]|$1|X|3|Y|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1J|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1K|8|@]|9|@]|A|$]]|$1|13|3|14|5|L|7|1L|8|@]|9|@]|A|$M|N]]|$1|15|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|16|$]]

<p>Here are some real-world examples of the types of relationships:</p>

<h2>One-to-one (1:1)</h2>

<p>A relationship is one-to-one if and only if one record from table A is related to a maximum of one record in table B.</p>

<p>To establish a one-to-one relationship, the primary key of table B (with no orphan record) must be the secondary key of table A (with orphan records).</p>

<p>For example:</p>

<pre><code>CREATE TABLE Gov(
    GID number(6) PRIMARY KEY, 
    Name varchar2(25), 
    Address varchar2(30), 
    TermBegin date,
    TermEnd date
); 

CREATE TABLE State(
    SID number(3) PRIMARY KEY,
    StateName varchar2(15),
    Population number(10),
    SGID Number(4) REFERENCES Gov(GID), 
    CONSTRAINT GOV_SDID UNIQUE (SGID)
);

INSERT INTO gov(GID, Name, Address, TermBegin) 
values(110, 'Bob', '123 Any St', '1-Jan-2009');

INSERT INTO STATE values(111, 'Virginia', 2000000, 110);
</code></pre>

<h2>One-to-many (1:M)</h2>

<p>A relationship is one-to-many if and only if one record from table A is
related to one or more records in table B. However, one record in table B cannot be related to more than one record in table A.</p>

<p>To establish a one-to-many relationship, the primary key of table A (the "one" table) must be the secondary key of table B (the "many" table).</p>

<p>For example:</p>

<pre><code>CREATE TABLE Vendor(
    VendorNumber number(4) PRIMARY KEY,
    Name varchar2(20),
    Address varchar2(20),
    City varchar2(15),
    Street varchar2(2),
    ZipCode varchar2(10),
    Contact varchar2(16),
    PhoneNumber varchar2(12),
    Status varchar2(8),
    StampDate date
);

CREATE TABLE Inventory(
    Item varchar2(6) PRIMARY KEY,
    Description varchar2(30),
    CurrentQuantity number(4) NOT NULL,
    VendorNumber number(2) REFERENCES Vendor(VendorNumber),
    ReorderQuantity number(3) NOT NULL
);
</code></pre>

<h2>Many-to-many (M:M)</h2>

<p>A relationship is many-to-many if and only if one record from table A is related to one or more records in table B and vice-versa.</p>

<p>To establish a many-to-many relationship, create a third table called "ClassStudentRelation" which will have the primary keys of both table A and table B.</p>

<pre><code>CREATE TABLE Class(
    ClassID varchar2(10) PRIMARY KEY, 
    Title varchar2(30),
    Instructor varchar2(30), 
    Day varchar2(15), 
    Time varchar2(10)
);

CREATE TABLE Student(
    StudentID varchar2(15) PRIMARY KEY, 
    Name varchar2(35),
    Major varchar2(35), 
    ClassYear varchar2(10), 
    Status varchar2(10)
);  

CREATE TABLE ClassStudentRelation(
    StudentID varchar2(15) NOT NULL,
    ClassID varchar2(14) NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID), 
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    UNIQUE (StudentID, ClassID)
);
</code></pre>


blocks|key|792096|text|一对多|type|unstyled|depth|inlineStyleRanges|entityRanges|data|792097|一对多表关系如下所示：|792098|​|792099|📷|atomic|offset|length|792100|792101|在关系数据库系统中，一对多表关系基于引用父表表行的Primary+Key的子表中的Foreign+Key列来链接两个表。|style|CODE|792102|在上图中，post_comment表中的post_id列与post表id+Primary+Key列具有Foreign+Key关系：|792103|ALTER+TABLE
++++post_comment
ADD+CONSTRAINT
++++fk_post_comment_post_id
FOREIGN+KEY+(post_id)+REFERENCES+post|code-block|syntax|javascript|792104|一对一|792105|一对一的表关系如下：|792106|792107|792108|792109|在关系数据库系统中，一对一的表关系基于子表中的Primary+Key列链接两个表，该列也是引用父表表行的Primary+Key的Foreign+Key。|792110|因此，我们可以说子表与父表共享Primary+Key。|792111|在上图中，post_details表中的id列与post表的id+Primary+Key列也具有Foreign+Key关系：|792112|ALTER+TABLE
++++post_details
ADD+CONSTRAINT
++++fk_post_details_id
FOREIGN+KEY+(id)+REFERENCES+post|792113|多对多|792114|多对多表关系如下所示：|792115|792116|792117|792118|在关系数据库系统中，多对多表关系通过子表链接两个父表，子表包含引用两个父表的Primary+Key列的两个Foreign+Key列。|792119|在上图中，post_tag表中的post_id列与post表id+Primary+Key列也具有Foreign+Key关系：|792120|ALTER+TABLE
++++post_tag
ADD+CONSTRAINT
++++fk_post_tag_post_id
FOREIGN+KEY+(post_id)+REFERENCES+post|792121|而且，post_tag表中的tag_id列与tag表id的Primary+Key列具有Foreign+Key关系：|792122|ALTER+TABLE
++++post_tag
ADD+CONSTRAINT
++++fk_post_tag_tag_id
FOREIGN+KEY+(tag_id)+REFERENCES+tag|792123|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/0179bd66be086a327acbcbe3db7cca07.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/b0e70f41cc5a836f19e7692fce275782.png|2|https://ask.qcloudimg.com/http-save/yehe-900000/f1fbf59ed3004c82723c0bc27ed9e204.png^0|0|0|0|0|1|0|0|0|P|B|15|B|0|5|C|K|7|T|4|11|B|1F|B|0|0|0|0|0|0|1|1|0|0|N|B|1G|B|1S|B|0|F|B|0|5|C|K|2|O|4|U|2|X|B|1C|B|0|0|0|0|0|0|1|2|0|0|12|B|1H|B|0|5|8|G|7|P|4|X|B|1C|B|0|0|3|8|E|6|M|3|T|B|17|B|0|0^^$0|@$1|2|3|4|5|6|7|25|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|26|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|27|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|28|8|@]|9|@$I|29|J|2A|1|2B]]|A|$]]|$1|K|3|E|5|6|7|2C|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|2D|8|@$I|2E|J|2F|N|O]|$I|2G|J|2H|N|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|2I|8|@$I|2J|J|2K|N|O]|$I|2L|J|2M|N|O]|$I|2N|J|2O|N|O]|$I|2P|J|2Q|N|O]|$I|2R|J|2S|N|O]]|9|@]|A|$]]|$1|R|3|S|5|T|7|2T|8|@]|9|@]|A|$U|V]]|$1|W|3|X|5|6|7|2U|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|2V|8|@]|9|@]|A|$]]|$1|10|3|E|5|6|7|2W|8|@]|9|@]|A|$]]|$1|11|3|G|5|H|7|2X|8|@]|9|@$I|2Y|J|2Z|1|30]]|A|$]]|$1|12|3|E|5|6|7|31|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|32|8|@$I|33|J|34|N|O]|$I|35|J|36|N|O]|$I|37|J|38|N|O]]|9|@]|A|$]]|$1|15|3|16|5|6|7|39|8|@$I|3A|J|3B|N|O]]|9|@]|A|$]]|$1|17|3|18|5|6|7|3C|8|@$I|3D|J|3E|N|O]|$I|3F|J|3G|N|O]|$I|3H|J|3I|N|O]|$I|3J|J|3K|N|O]|$I|3L|J|3M|N|O]|$I|3N|J|3O|N|O]]|9|@]|A|$]]|$1|19|3|1A|5|T|7|3P|8|@]|9|@]|A|$U|V]]|$1|1B|3|1C|5|6|7|3Q|8|@]|9|@]|A|$]]|$1|1D|3|1E|5|6|7|3R|8|@]|9|@]|A|$]]|$1|1F|3|E|5|6|7|3S|8|@]|9|@]|A|$]]|$1|1G|3|G|5|H|7|3T|8|@]|9|@$I|3U|J|3V|1|3W]]|A|$]]|$1|1H|3|E|5|6|7|3X|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|3Y|8|@$I|3Z|J|40|N|O]|$I|41|J|42|N|O]]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|43|8|@$I|44|J|45|N|O]|$I|46|J|47|N|O]|$I|48|J|49|N|O]|$I|4A|J|4B|N|O]|$I|4C|J|4D|N|O]]|9|@]|A|$]]|$1|1M|3|1N|5|T|7|4E|8|@]|9|@]|A|$U|V]]|$1|1O|3|1P|5|6|7|4F|8|@$I|4G|J|4H|N|O]|$I|4I|J|4J|N|O]|$I|4K|J|4L|N|O]|$I|4M|J|4N|N|O]|$I|4O|J|4P|N|O]]|9|@]|A|$]]|$1|1Q|3|1R|5|T|7|4Q|8|@]|9|@]|A|$U|V]]|$1|1S|3|-4|5|6|7|4R|8|@]|9|@]|A|$]]]|1T|$1U|$5|1V|1W|1X|A|$1Y|1Z|20|-4]]|21|$5|1V|1W|1X|A|$1Y|22|20|-4]]|23|$5|1V|1W|1X|A|$1Y|24|20|-4]]]]

<h2>One-to-many</h2>
<p>The one-to-many table relationship looks as follows:</p>
<p><a href="https://i.stack.imgur.com/Gxitd.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Gxitd.png" alt="One-to-many" /></a></p>
<p>In a relational database system, a one-to-many table relationship links two tables based on a <code>Foreign Key</code> column in the child which references the <code>Primary Key</code> of the parent table row.</p>
<p>In the table diagram above, the <code>post_id</code> column in the <code>post_comment</code> table has a <code>Foreign Key</code> relationship with the <code>post</code> table id <code>Primary Key</code> column:</p>
<pre><code>ALTER TABLE
    post_comment
ADD CONSTRAINT
    fk_post_comment_post_id
FOREIGN KEY (post_id) REFERENCES post
</code></pre>
<h2>One-to-one</h2>
<p>The one-to-one table relationship looks as follows:</p>
<p><a href="https://i.stack.imgur.com/WOocn.png" rel="noreferrer"><img src="https://i.stack.imgur.com/WOocn.png" alt="One-to-one" /></a></p>
<p>In a relational database system, a one-to-one table relationship links two tables based on a <code>Primary Key</code> column in the child which is also a <code>Foreign Key</code> referencing the <code>Primary Key</code> of the parent table row.</p>
<p>Therefore, we can say that the child table shares the <code>Primary Key</code> with the parent table.</p>
<p>In the table diagram above, the <code>id</code> column in the <code>post_details</code> table has also a <code>Foreign Key</code> relationship with the <code>post</code> table <code>id</code> <code>Primary Key</code> column:</p>
<pre><code>ALTER TABLE
    post_details
ADD CONSTRAINT
    fk_post_details_id
FOREIGN KEY (id) REFERENCES post
</code></pre>
<h2>Many-to-many</h2>
<p>The many-to-many table relationship looks as follows:</p>
<p><a href="https://i.stack.imgur.com/2tXIa.png" rel="noreferrer"><img src="https://i.stack.imgur.com/2tXIa.png" alt="Many-to-many" /></a></p>
<p>In a relational database system, a many-to-many table relationship links two parent tables via a child table which contains two <code>Foreign Key</code> columns referencing the <code>Primary Key</code> columns of the two parent tables.</p>
<p>In the table diagram above, the <code>post_id</code> column in the <code>post_tag</code> table has also a <code>Foreign Key</code> relationship with the <code>post</code> table id <code>Primary Key</code> column:</p>
<pre><code>ALTER TABLE
    post_tag
ADD CONSTRAINT
    fk_post_tag_post_id
FOREIGN KEY (post_id) REFERENCES post
</code></pre>
<p>And, the <code>tag_id</code> column in the <code>post_tag</code> table has a <code>Foreign Key</code> relationship with the <code>tag</code> table id <code>Primary Key</code> column:</p>
<pre><code>ALTER TABLE
    post_tag
ADD CONSTRAINT
    fk_post_tag_tag_id
FOREIGN KEY (tag_id) REFERENCES tag
</code></pre>


<p>Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?</p>


How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

数据库

有没有人能举例说明如何在设计表格时实现一对一、一对多和多对多关系？

问如何在设计表格时实现一对一、一对多、多对多关系？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在设计表格时实现一对一、一对多、多对多关系？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在设计表格时实现一对一、一对多、多对多关系？
EN