SQL 如何在两个表之间共享相同的主键？

How can I share the same primary key across two tables?

提问by Only Bolivian Here

I'm reading a book on EF4and I came across this problem situation:

我正在阅读一本关于EF4的书，我遇到了这个问题情况：

So I was wondering how to create this database so I can follow along with the example in the book.

所以我想知道如何创建这个数据库，以便我可以按照书中的示例进行操作。

How would I create these tables, using simple TSQL commands? Forget about creating the database, imagine it already exists.

我将如何使用简单的 TSQL 命令创建这些表？忘记创建数据库，想象它已经存在。

采纳答案by Jaymz

When it says the tables share the same primary key, it just means that there is a field with the same name in each table, both set as Primary Keys.

当它说表共享相同的主键时，这只是意味着每个表中有一个名称相同的字段，都设置为主键。

Create Tables

创建表

CREATE TABLE [Product (Chapter 2)](
    SKU varchar(50) NOT NULL,
    Description varchar(50) NULL,
    Price numeric(18, 2) NULL,
    CONSTRAINT [PK_Product (Chapter 2)] PRIMARY KEY CLUSTERED 
    (
        SKU ASC
    )
)

CREATE TABLE [ProductWebInfo (Chapter 2)](
    SKU varchar(50) NOT NULL,
    ImageURL varchar(50) NULL,
    CONSTRAINT [PK_ProductWebInfo (Chapter 2)] PRIMARY KEY CLUSTERED 
    (
        SKU ASC
    )
)

Create Relationships

建立关系

ALTER TABLE [ProductWebInfo (Chapter 2)] 
    ADD CONSTRAINT fk_SKU 
    FOREIGN KEY(SKU)
REFERENCES [Product (Chapter 2)] (SKU)

It may look a bit simpler if the table names are just single words (and not key words, either), for example, if the table names were just Productand ProductWebInfo, without the (Chapter 2)appended:

如果表名只是单个单词（也不是关键字），则看起来可能会更简单一些，例如，如果表名只是Productand ProductWebInfo，而没有(Chapter 2)附加：

ALTER TABLE ProductWebInfo
    ADD CONSTRAINT fk_SKU
    FOREIGN KEY(SKU)
REFERENCES Product(SKU)

回答by HLGEM

You've been given the code. I want to share some information on why you might want to have two tables in a relationship like that.

你已经得到了代码。我想分享一些关于为什么您可能希望在这样的关系中拥有两个表的信息。

First when two tables have the same Primary Key and have a foreign key relationship, that means they have a one-to-one relationship. So why not just put them in the same table? There are several reasons why you might split some information out to a separate table.

首先，当两个表具有相同的主键并且具有外键关系时，这意味着它们具有一对一的关系。那么为什么不把它们放在同一张桌子上呢？您可能会出于多种原因将某些信息拆分到单独的表中。

First the information is conceptually separate. If the information contained in the second table relates to a separate specific concern, it makes it easier to work with it the data is in a separate table. For instance in your example they have separated out images even though they only intend to have one record per SKU. This gives you the flexibility to easily change the table later to a one-many relationship if you decide you need multiple images. It also means that when you query just for images you don't have to actually hit the other (perhaps significantly larger) table.

首先，信息在概念上是分开的。如果第二个表中包含的信息与单独的特定关注点相关，则数据位于单独的表中会使其更易于使用。例如，在您的示例中，即使他们只打算每个 SKU 有一个记录，他们也已分离出图像。如果您决定需要多个图像，这使您可以灵活地稍后轻松地将表更改为一对多关系。这也意味着当您仅查询图像时，您不必实际访问另一个（可能显着更大）的表。

Which bring us to reason two to do this. You currently have a one-one relationship but you know that a future release is already scheduled to turn that to a one-many relationship. In this case it's easier to design into a separate table, so that you won't break all your code when you move to that structure. If I were planning to do this I would go ahead and create a surrogate key as the PK and create a unique index on the FK. This way when you go to the one-many relationship, all you have to do is drop the unique index and replace it with a regular index.

这让我们有两个理由这样做。您目前拥有一对一的关系，但您知道未来的版本已安排将其转变为一对多的关系。在这种情况下，设计一个单独的表会更容易，这样当您移动到该结构时就不会破坏所有代码。如果我打算这样做，我会继续创建一个代理键作为 PK，并在 FK 上创建一个唯一索引。这样，当您进入一对多关系时，您所要做的就是删除唯一索引并将其替换为常规索引。

Another reason to separate out a one-one relationship is if the table is getting too wide. Sometimes you just have too much information about an entity to easily fit it in the maximum size a record can have. In this case, you tend to take the least used fields (or those that conceptually fit together) and move them to a separate table.

分离一对一关系的另一个原因是桌子太宽了。有时，您只是拥有太多关于实体的信息，无法轻松地将其放入记录可以拥有的最大大小中。在这种情况下，您倾向于采用最少使用的字段（或那些在概念上适合的字段）并将它们移动到单独的表中。

Another reason to separate them out is that although you have a one-one relationship, you may not need a record of what is in the child table for most records in the parent table. So rather than having a lot of null values in the parent table, you split it out.

将它们分开的另一个原因是，尽管您有一对一的关系，但对于父表中的大多数记录，您可能不需要子表中的记录。因此，与其在父表中包含大量空值，不如将其拆分。

The code shown by the others assumes a character-based PK. If you want a relationship of this sort when you have an auto-generating Int or GUID, you need to do the autogeneration only on the parent table. Then you store that value in the child table rather than generating a new one on that table.

其他人显示的代码假定基于字符的 PK。如果您在拥有自动生成的 Int 或 GUID 时想要这种关系，则只需在父表上执行自动生成。然后将该值存储在子表中，而不是在该表上生成一个新值。

回答by Bobby D

This simply an example that I threw together using the table designer in SSMS, but should give you an idea (note the foreign key constraint at the end):

这只是我使用 SSMS 中的表设计器拼凑的一个示例，但应该给你一个想法（注意最后的外键约束）：

CREATE TABLE dbo.Product
    (
    SKU int NOT NULL IDENTITY (1, 1),
    Description varchar(50) NOT NULL,
    Price numeric(18, 2) NOT NULL
    )  ON [PRIMARY]

ALTER TABLE dbo.Product ADD CONSTRAINT
    PK_Product PRIMARY KEY CLUSTERED 
    (
    SKU
    )

CREATE TABLE dbo.ProductWebInfo
    (
    SKU int NOT NULL,
    ImageUrl varchar(50) NULL
    )  ON [PRIMARY]

ALTER TABLE dbo.ProductWebInfo ADD CONSTRAINT
    FK_ProductWebInfo_Product FOREIGN KEY
    (
    SKU
    ) REFERENCES dbo.Product
    (
    SKU
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

回答by JeffO

See how to create a foreign key constraint. http://msdn.microsoft.com/en-us/library/ms175464.aspxThis also has links to creating tables. You'll need to create the database as well.

了解如何创建外键约束。http://msdn.microsoft.com/en-us/library/ms175464.aspx这也有创建表的链接。您还需要创建数据库。

To answer your question:

回答你的问题：

ALTER TABLE ProductWebInfo
ADD CONSTRAINT fk_SKU
FOREIGN KEY (SKU)
REFERENCES Product(SKU)