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)?
ClassExpectations
, not 4, both composite.