2

How do I create a composite foreign key to a table with a single primary key? For example suppose I have the following tables:

CREATE TABLE Branches (
    BranchId UniqueIdentifier DEFAULT NewSequentialID(), 
    CONSTRAINT pk_BranchId PRIMARY KEY (BranchId)
);

CREATE TABLE Expectations (
    ExpectationId UniqueIdentifier DEFAULT NewSequentialID(), 
    BranchId UniqueIdentifier NOT NULL, 

    CONSTRAINT fk_Expectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId), 
    CONSTRAINT pk_ExpectationId PRIMARY KEY (ExpectationId)
);

CREATE TABLE ClassSections (
    ClassId UniqueIdentifier NOT NULL,
    BranchId UniqueIdentifier NOT NULL, 

    CONSTRAINT fk_ClassSections_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId), 
    CONSTRAINT fk_ClassSections_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId), 
    CONSTRAINT pk_ClassSections PRIMARY KEY (ClassID, BranchId)
);

CREATE TABLE ClassExpectations (
    ClassId UniqueIdentifier NOT NULL, 
    BranchId UniqueIdentifier NOT NULL, 
    ExpectationId UniqueIdentifier NOT NULL, 

    CONSTRAINT fk_ClassExpectations_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId), 
    CONSTRAINT fk_ClassExpectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId), 
    CONSTRAINT fk_ClassExpectations_ExpectationId FOREIGN KEY (ExpectationId) REFERENCES Expectations(ExpectationId), 
    CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId), 
    CONSTRAINT pk_ClassDueDates PRIMARY KEY (ClassId, ExpectationId)
);

I'm trying to add the following constraint, but it won't allow me:

ALTER TABLE ClassExpectations ADD CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId);

I keep getting the error:

There are no primary or candidate keys in the referenced table 'Expectations' that match the referencing column list in the foreign key

How else do I ensure that an ExpectationId is never added to ClassExpectations without its corresponding BranchId (from the Expectations table)?

1

1 Answer 1

4

The answer is a little bit like the old joke: "Doctor, doctor! It hurts when I do this."

If you want a foreign key to an entity then that entity needs to have a unique key defined to be referenced.

The fact that your Expectations table has a unique candidate key in Expectations.ExpectationId should not prevent you from defining another candidate key that combines ExpectationId and BranchId. This seems like a violation of third normal form, and technically it is. However, that is only true because you happen to define ExpectationId as unique. You could have defined ExpectationId as a sequence within BranchId, such that ExpectationId is not unique on its own. As it is, you can think of it as the Expectation table defining the combination of two unique values as being the fact of interest.

In any case, if you want to define a foreign key to Expectation that is the combination of ExpectationId and BranchId then there is no way around it but to define the primary key that way.

Not the answer you're looking for? Browse other questions tagged or ask your own question.