复合主键sql关系 [英] Composite primary key sql relationship

问题描述

我需要在 ReportID 之间创建一个关系，它是复合主键的一部分.这是我为 Microsoft SQL Server 编写的代码:

I need to create a relationship between ReportID which is part of a composite primary key. This is my code written for Microsoft SQL Server:

CREATE TABLE Tableone
(
    ReportID varchar UNIQUE,
    Date date,
    Line ommited

PRIMARY KEY (ReportNumber, Date)
)

CREATE TABLE Tabletwo
(
    Line omitted
    Line ommited
    ReportID varchar UNIQUE FOREIGN KEY REFERENCES Tableone(ReportID),
    Line ommited

    PRIMARY KEY (XX, XXX, ReportID)
)

更新:我们特别要求将 ReportID 和 Date 设置为 Tableone 中的主键，但只引用 Tabletwo中的ReportID.

UPDATE: We have specifically been asked to set both ReportID and Date as the primary key in Tableone but to just reference ReportID in Tabletwo.

感谢您的时间.

推荐答案

如果您有一个复合主键，那么引用它的所有外键约束必须使用<复合主键的强>所有列.

If you have a composite primary key, then all foreign key constraints that reference it must use all columns of the composite primary key.

因此在您的情况下 - TableTwo 必须 引用 TableOne(ReportID, Date)

So in your case - TableTwo must reference TableOne(ReportID, Date)

解决此问题的唯一方法是在 TableOne (ReportID) 上创建一个 UNIQUE INDEX - 然后您可以单独创建对该唯一约束的外键引用.

The only way around this would be to create a UNIQUE INDEX on TableOne (ReportID) - then you can create a foreign key reference to that unique constraint alone.

但这又引出了一个问题:如果 ReportID 本身已经是唯一的(因为您可以在其上放置一个 UNIQUE INDEX)和NOT NULL(因为它是复合 PK 的一部分)?

But that then begs the question: why isn't ReportID alone the primary key, if it's already unique (since you can put a UNIQUE INDEX on it) and NOT NULL (since it's part of the compound PK) ?

第一步:创建UNIQUE INDEX:

CREATE UNIQUE NONCLUSTERED INDEX UIX_TableOne ON dbo.TableOne(ReportID);

第 2 步:从 TableTwo 创建外键:

Step 2: create the foreign key from TableTwo:

ALTER TABLE dbo.TableTwo
ADD CONSTRAINT FK_TableTwo_TableOne
FOREIGN KEY(ReportID) REFERENCES dbo.TableOne(ReportID)

这篇关于复合主键sql关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！