I have four tables (simplified)

CREATE TABLE ProductDetails 
(
    ProductID int,
    ProductName varchar(50),
    CONSTRAINT PK_ProductDetails PRIMARY KEY (ProductID)
);

CREATE TABLE CaseDetails  -- Cases contain a given product. There can be
                          -- many cases for any given product, 
                          -- but only one product per case
(
    CaseID int,
    ProductID int,
    CONSTRAINT PK_CaseDetails PRIMARY KEY (CaseID),
    CONSTRAINT FK_CaseDetails_ProductID 
        FOREIGN KEY (ProductID) REFERENCES ProductDetails(ProductID)
);

CREATE TABLE ProductRun 
(
    ProductID int,
    StartDate date,
    EndDate date,
    CONSTRAINT PK_ProductRun PRIMARY KEY (ProductID, StartDate),
    CONSTRAINT FK_ProductRun_ProductID 
        FOREIGN KEY (ProductID) REFERENCES ProductDetails(ProductID)
);

CREATE TABLE CasesNeeded 
(
    ProductID int,
    StartDate date,
    CaseID int,
    CasesNeeded int,
    CONSTRAINT PK_CasesNeeded PRIMARY KEY (ProductID, StartDate, CaseID),
    CONSTRAINT FK_CasesNeeded_ProductRun 
        FOREIGN KEY (ProductID, StartDate) REFERENCES ProductRun(ProductID, StartDate)
);

I would like to constrain the CasesNeeded.CaseID column to only be able to take one of the Case.CaseID values that has the same Case.ProductID as the CasesNeeded.ProductID.

I tried using a subquery in a check constraint like:

CONSTRAINT FK_CasesNeeded_CaseID 
    CHECK (ProductID = (SELECT ProductID 
                        FROM CaseDetails 
                        WHERE CaseDetails.CaseID = CaseDetails.CaseID))

But I get an error

Subqueries are not allowed in this context. Only scalar expressions are allowed

I would like to avoid user-defined functions if at all possible because I have heard they are slow and unreliable.

I have four tables (simplified)

CREATE TABLE ProductDetails 
(
    ProductID int,
    ProductName varchar(50),
    CONSTRAINT PK_ProductDetails PRIMARY KEY (ProductID)
);

CREATE TABLE CaseDetails  -- Cases contain a given product. There can be
                          -- many cases for any given product, 
                          -- but only one product per case
(
    CaseID int,
    ProductID int,
    CONSTRAINT PK_CaseDetails PRIMARY KEY (CaseID),
    CONSTRAINT FK_CaseDetails_ProductID 
        FOREIGN KEY (ProductID) REFERENCES ProductDetails(ProductID)
);

CREATE TABLE ProductRun 
(
    ProductID int,
    StartDate date,
    EndDate date,
    CONSTRAINT PK_ProductRun PRIMARY KEY (ProductID, StartDate),
    CONSTRAINT FK_ProductRun_ProductID 
        FOREIGN KEY (ProductID) REFERENCES ProductDetails(ProductID)
);

CREATE TABLE CasesNeeded 
(
    ProductID int,
    StartDate date,
    CaseID int,
    CasesNeeded int,
    CONSTRAINT PK_CasesNeeded PRIMARY KEY (ProductID, StartDate, CaseID),
    CONSTRAINT FK_CasesNeeded_ProductRun 
        FOREIGN KEY (ProductID, StartDate) REFERENCES ProductRun(ProductID, StartDate)
);

I would like to constrain the CasesNeeded.CaseID column to only be able to take one of the Case.CaseID values that has the same Case.ProductID as the CasesNeeded.ProductID.

I tried using a subquery in a check constraint like:

CONSTRAINT FK_CasesNeeded_CaseID 
    CHECK (ProductID = (SELECT ProductID 
                        FROM CaseDetails 
                        WHERE CaseDetails.CaseID = CaseDetails.CaseID))

But I get an error

Subqueries are not allowed in this context. Only scalar expressions are allowed

I would like to avoid user-defined functions if at all possible because I have heard they are slow and unreliable.

• sql-server
• constraints
• sql-server-2019

Share Follow edited Mar 8 at 8:03 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 7 at 19:26 Brady HinmanBrady Hinman 2111 silver badge22 bronze badges 4

• Is this WHERE CaseDetails.CaseID = CaseDetails.CaseID special trik or typo? – ValNik Commented Mar 7 at 21:43
• You have provided definitions for tables ProductDetails, CaseDetails, ProductRun, and CasesNeeded, but then you reference Case.CaseID and Case.ProductID. It is not clear what Case is. – T N Commented Mar 7 at 22:59
• In your case a function should do the trick. Or you can always write a trigger. – Alex Commented Mar 8 at 5:22
• 1 Isn't this just another FK – siggemannen Commented Mar 8 at 11:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

You can just add a unique constraint or unique index to CaseDetails keyed on both those columns, then you can foreign-key onto it.

ALTER TABLE CaseDetails
ADD CONSTRAINT UQ_CaseDetails
    UNIQUE (CaseID, ProductID);
-- alternatively, good idea if you want INCLUDE columns
CREATE NONCLUSTERED UNIQUE INDEX UQ_CaseDetails
    ON CaseDetails (CaseID, ProductID);


ALTER TABLE CasesNeeded
ADD CONSTRAINT FK_CasesNeeded_CaseID
    FOREIGN KEY (CaseID, ProductID) REFERENCES CaseDetails(CaseID, ProductID)