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

I'm reading a book on EF4 and 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命令创建这些表?忘记创建数据库，假设它已经存在。

4 个解决方案

#1

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 Product and ProductWebInfo, without the (Chapter 2) appended:

例如，如果表名只是单个单词(也不是关键字)，如果表名只是Product和ProductWebInfo，而没有附加(第2章):

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

#2

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.

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

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。然后将该值存储在子表中，而不是在该表上生成一个新值。

#3

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 

#4

See how to create a foreign key constraint. http://msdn.microsoft.com/en-us/library/ms175464.aspx This 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)