SQL 如何识别数据库关系中的父子表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29561123/
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
How can I Identify Parent and Child Table in a Database Relationship
提问by Anynomous Khan
Although this topic must have been asked by many people so it is must to inform that I am not asking for a query!
虽然这个话题一定有很多人问过所以必须通知我不是在询问！
The question is theoretical that how can one differentiate in a database as to which table is the parent, and which is the child?
问题是理论上的，如何在数据库中区分哪个表是父表，哪个是子表？
From what I learned is that the table having primary key is usually the parent table (also called Look up Table) and the one with the foreign key is the child table. Am I right?
从我了解到的是，具有主键的表通常是父表（也称为查找表），带有外键的表是子表。我对吗？
回答by StuartLC
In most cases, if the tables have referential integrityin place, you can examine the foreign keys to identify parent-child relationships. The child table has the foreign key which references the parent. This way, all children of the same parent will have the same value for the foreign key.
在大多数情况下，如果表具有参照完整性，您可以检查外键以识别父子关系。子表具有引用父表的外键。这样，同一父级的所有子级都将具有相同的外键值。
For example in the following relationship:
例如在以下关系中：
CREATE TABLE Table1
(
-- Key for Parent
Table1ID INT CONSTRAINT PK_Table1 PRIMARY KEY,
);
CREATE TABLE Table2
(
-- Key for Child
Table2ID INT CONSTRAINT PK_Table2 PRIMARY KEY,
-- Foreign Key to Parent
Table1ID INT CONSTRAINT FK_Table2_Table1 REFERENCES Table1(Table1ID)
);
And in diagrams:
在图表中：
Table2is the child of Table1, because Table2has the foreign key to its parent. (Apologies for the names, but calling the tables Parentand Childwould defeat the analysis).
Table2是 的子代Table1，因为Table2具有其父代的外键。（道歉的名字，但在调用表Parent，并Child会破坏分析）。
If there are no foreign keys
如果没有外键
This can be more difficult. Have a look for evidence such as:
这可能更困难。寻找证据，例如：
- Column naming conventions - often foreign key columns are of the form
<TableName>Id- this might help. - Usage - look for other database entities like views, store procedures, rules or functions, and specifically, for how tables are
JOINed together - this may be useful in determining the relationship between the tables.
- 列命名约定 - 通常是外键列的形式
<TableName>Id- 这可能会有所帮助。 - 用法 - 查找其他数据库实体，如视图、存储过程、规则或函数，特别是如何将表
JOIN组合在一起 - 这可能有助于确定表之间的关系。
False Positives
误报
In "OO" terms, database parent-child relationships would usually be equivalent to OO Composition, i.e. the child cannot exist without the parent.
在“OO”术语中，数据库父子关系通常等同于 OO 组合，即没有父项，子项就不能存在。
Note however that database foreign keys are also commonly used for:
但请注意，数据库外键也常用于：
- Non parent-child relationships, such as Classification
- 非父子关系，例如分类
e.g.
例如
CREATE TABLE Person
(
...
LocationId INT FOREIGN KEY REFERENCES Country(CountryId)
)
Here, both person AND country could logically exist without each other, so this relationship is more like an OO Aggregate or Association relationship, rather than a parent-child relationship.
在这里，person AND country 在逻辑上可以彼此独立存在，因此这种关系更像是 OO Aggregate 或 Association 关系，而不是父子关系。
- Inheritance and extension tables
- 继承和扩展表
Both OO inheritance and database table 'extension' patterns are modelled slightly differently to parent-child, as the second table's primary key is also a foreign key to the parent tables' primary key, i.e. a row in Table2must have a single, corresponding row in Table1, so the cardinality between Table1 to Table2 is 1 to 0..1, whereas parent-child would be 1 to 0..n
OO 继承和数据库表“扩展”模式的建模与父子表略有不同，因为第二个表的主键也是父表主键的外键，即行中Table2必须有一个对应的行Table1，所以 Table1 到 Table2 之间的基数是1 to 0..1，而父子是1 to 0..n
CREATE TABLE Table1
(
-- Key for Parent
Table1ID INT CONSTRAINT PK_Table1 PRIMARY KEY,
);
CREATE TABLE Table2
(
-- Table2's primary key is ALSO a FOREIGN key to Table 1
SomeId INT CONSTRAINT PK_Table2 PRIMARY KEY,
CONSTRAINT FK_Table2_Table1 FOREIGN KEY (SomeId) REFERENCES Table1(Table1ID)
);
A dead giveaway for spotting OO relationships in database models is the existence of multiple 'subclass' tables which re-use, AND reference, the base table's primary key.
在数据库模型中发现 OO 关系的一个致命赠品是存在多个“子类”表，这些表重用和引用基表的主键。
In table extension (usually a design smell), there will likely be an unusually large number of columns in Table1which then overflow into Table2(The Siebel customer tables come to mind).
在表扩展中（通常是一种设计味道），可能会有异常多的列Table1，然后会溢出到Table2（想到 Siebel 客户表）。
回答by David Aldridge
A parent-child relationship is defined by the presence of a foreign key constraint. The constraint is generally created on the child table, but that's just syntactical -- the constraint really exists as its own entity and is linked to both tables.
父子关系由外键约束的存在定义。约束通常是在子表上创建的，但这只是语法上的——约束确实作为它自己的实体存在并链接到两个表。
The child table would have one or more columns which relate to one or more columns on the parent table. The parent table column(s) must have a primary or unique constraint place on them.
子表将有一个或多个与父表上的一或多个列相关的列。父表列必须具有主要或唯一约束位置。
