表与许多表具有一对一的关系

1) Can a table have a one-to-one relationship with a number of tables !?

1）表格可以与多个表格保持一对一的关系！

To clarify more, if I want to do an insert the first table will be affected and only one of the other tables will be affected .

为了澄清更多，如果我想进行插入，第一个表将受到影响，其他表中只有一个会受到影响。

2) and if so, how the primary key will be ?

2）如果是这样，主键将如何？

3) also, what will the query look like if I want to retrieve a number of records from these tables ?

3）此外，如果我想从这些表中检索大量记录，查询会是什么样子？

Thank you .

谢谢。

2 个解决方案

#1

Can a table have a one-to-one relationship with a number of tables !?

表可以与多个表具有一对一的关系！

Yes if you really mean 1:0/1:

是的，如果你真的是1：0/1：

Create Table Parent
    (
    Id ... not null Primary Key
    , ...
    )

Create Table Child1
    (
    Id ... not null Primary Key
    , Foreign Key ( Id ) References Parent ( Id )
    ...
    )

Create Table Child2
    (
    Id ... not null Primary Key
    , Foreign Key ( Id ) References Parent ( Id )
    ...
    )

This setup would require entering a value into the Parent table first and then the child tables (in no particular order) afterwards. Further, you could add a value to one of the Child tables and not the other since they both only rely on the existence of a value in the Parent table and not each other.

此设置需要先在父表中输入一个值，然后再输入子表（不按特定顺序）。此外，您可以将一个值添加到其中一个子表而不是另一个，因为它们都只依赖于父表中存在的值而不是彼此。

Addition

加成

To select from your child tables, it would involve the same process as any other parent-child relationship. For example:

要从子表中进行选择，它将涉及与任何其他父子关系相同的过程。例如：

Select P.Col1, P.Col2...
    , Child1.Col1, Child1.Col2...
From Parent
    Inner Join Child1
        On Child1.FKCol = Parent.PKCol

By using an Inner Join here, i'm only return Parent rows where there exists a Child row. If you wanted all Parent rows and only those Child rows where there is a match, you would use a Left Join instead of an Inner Join. If you wanted to select data from more than one child table at the same time, you can simply include those in the From clause:

通过在这里使用内部联接，我只返回存在子行的父行。如果您想要所有父行以及只有那些匹配的子行，您将使用左连接而不是内连接。如果要同时从多个子表中选择数据，可以在From子句中包含这些数据：

Select P.Col1, P.Col2...
    , Child1.Col1, Child1.Col2...
    , Child2.Col1, Child2.Col2...
    , Child3.Col1, Child3.Col2...
From Parent
    Left Join Child1
        On Child1.FKCol = Parent.PKCol
    Left Join Child2
        On Child2.FKCol = Parent.PKCol
    Left Join Child3
        On Child2.FKCol = Parent.PKCol

#2

In SQL Server you could certainly design a database that is capable of representing this relationship. You could enforce the one to one relationship by having the child tables use the ParentId as their primary key and force uniqueness.

在SQL Server中，您当然可以设计一个能够表示此关系的数据库。您可以通过让子表使用ParentId作为主键并强制唯一性来强制执行一对一关系。

If you wanted to query a parent table and it's three children that may or may not have existing records your query would look something like this:

如果您想查询父表，并且它的三个子节点可能有也可能没有现有记录，您的查询将如下所示：

SELECT * FROM ParentTable as pt
LEFT JOIN ChildTable1 as ct1
ON pt.id = ct1.ParentId
LEFT JOIN ChildTable2 as ct2
ON pt.id = ct2.ParentId
LEFT JOIN ChildTable3 as ct3
ON pt.ID = ct3.ParentId

My question would be why would you break a one to one relationship into multiple tables? You could also enforce a one to one relationship with the data if you kept it all in one table. This would make for cleaner queries (no joins) and better performance.

我的问题是你为什么要将一对一的关系分成多个表？如果将所有数据保存在一个表中，您还可以强制实施与数据的一对一关系。这样可以实现更清晰的查询（无连接）和更好的性能。

#1