Composite primary key sql relationship

Question

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)
)

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.

Thanks for your time.

Answer 1

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

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

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.

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) ?

Step 1: create the UNIQUE INDEX:

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

Step 2: create the foreign key from TableTwo:

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