SQL Server: Constrain Column based on Column in other table

Question

I'm trying to constrain values of one table based on values from another. For T1, column 'col' is allowed to be 'A' or 'B'. For T2 column 'col' is allowed to be 'C' or 'D'. But T2.col is not allowed to be 'D' if T1.col is 'B'. They have a many to one relationship of T1.PK = T2.FK. If I try to insert D in T2.col when its joining T1.col = 'B', it should fail.

I saw this but I'm not trying to do any aggregate function. My current attempt looks like this

CREATE FUNCTION dbo.CheckAddition()
RETURNS bit
AS BEGIN RETURN (
    SELECT CASE 
        WHEN T2.col = 'C' THEN 1
        WHEN T2.col = 'D' AND T1.col = 'A' THEN 1
        ELSE 0 
    END AS 'Check'
    FROM T1 
    INNER JOIN T2
    ON T1.PK = T2.FK
) END

GO;
ALTER TABLE Shift ADD CONSTRAINT checkAdd CHECK (dbo.CheckAddition() = 1);

but since it's not an aggregate, I get this failure.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Edit
My full tables look like this

CREATE TABLE [dbo].[T1](
    [PK] [bigint] IDENTITY(1,1) NOT NULL,
    [col] [char](1) NOT NULL,
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 
(
    [PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[T1]  WITH CHECK ADD  CONSTRAINT [CK_T1] CHECK  (([col]='B' OR [col]='A'))
GO

ALTER TABLE [dbo].[T1] CHECK CONSTRAINT [CK_T1]
GO


CREATE TABLE [dbo].[T2](
    [PK] [bigint] IDENTITY(1,1) NOT NULL,
    [FK] [bigint] NOT NULL,
    [col] [char](1) NOT NULL,
 CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED 
(
    [PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[T2]  WITH CHECK ADD  CONSTRAINT [FK_T2_T1] FOREIGN KEY([FK])
REFERENCES [dbo].[T1] ([PK])
GO

ALTER TABLE [dbo].[T2] CHECK CONSTRAINT [FK_T2_T1]
GO

ALTER TABLE [dbo].[T2]  WITH CHECK ADD  CONSTRAINT [CK_T2] CHECK  (([col]='D' OR [col]='C'))
GO

ALTER TABLE [dbo].[T2] CHECK CONSTRAINT [CK_T2]
GO

I'm trying to make it so T2.col can not be 'D', if the matching PK/FK in T1.col = 'A'

I would probably want Min() or to structure my cases separate, but if the table grows to millions of rows and multiple inserts per second, couldn't that cause a problem if it has to run every time? — Faller, Mar 20 '19 at 13:54
It is absolutely a potential performance issue. But you have a scalar function as a constraint reading two tables seemingly unrelated to the table where the constraint is. — Sean Lange, Mar 20 '19 at 13:56
You function is an scalar function, so, you need to ensure that in all possible scenarios will only return a single value... — Daniel Brughera, Mar 20 '19 at 14:02
@HasanMahmood, that works well as long as I order descending by PK. — Faller, Mar 20 '19 at 14:12
@Faller you are creating a function, I think there are some more condition needed, you just can not check all rows in a function, it might get performance issue, can you please describe your full requirements. — Hasan Mahmood, Mar 20 '19 at 14:14

score 1 · Accepted Answer · answered Mar 20 '19 at 14:17

That fix the error you are getting

CREATE FUNCTION dbo.CheckAddition()
RETURNS bit
AS BEGIN 
DECLARE @Res BIT

    SELECT @Res = CASE 
        WHEN T2.col = 'C' THEN 1
        WHEN T2.col = 'D' AND T1.col = 'A' THEN 1
        ELSE 0 
    END 
    FROM T1 
    INNER JOIN T2
    ON T1.PK = T2.FK
    RETURN @Res 
END

GO;
ALTER TABLE Shift ADD CONSTRAINT checkAdd CHECK (dbo.CheckAddition() = 1);

But for performance an safety is better to tho this....

CREATE FUNCTION dbo.CheckAddition(@FKValue INT, @Value CHAR(1))
    RETURNS bit
    AS BEGIN 
    DECLARE @Res BIT

        SELECT @Res = CASE 
            WHEN @Value = 'D' AND T1.Col = 'B' THEN 0
            ELSE 1 
        END 
        FROM T1
        WHERE T1.FK = @FKValue
        RETURN @Res 
    END

    GO;

ALTER TABLE T2 ADD CONSTRAINT checkValue CHECK (Col IN ('C','D'));
ALTER TABLE T2 ADD CONSTRAINT checkAdd CHECK (dbo.CheckAddition(FK, Col) = 1);

The check runs before inserting the value, so, if your function checks for the commited values, it wont return an error when you are setting a wrong value, but it will when you try to change it to a valid one... This way you are sending the values to be validated prior to be inserted...

Awesome. This is exactly what I was looking for. – Faller Mar 20 '19 at 14:41 — Faller, Mar 20 '19 at 14:41

score 0 · Answer 2 · answered Mar 20 '19 at 14:15

0

Using @HasanMahmood's suggestion, this works.

CREATE FUNCTION dbo.CheckAddition()
RETURNS bit
AS BEGIN RETURN (
    SELECT TOP 1 CASE 
        WHEN T2.col = 'C' THEN 1
        WHEN T2.col = 'D' AND T1.col = 'A' THEN 1
        ELSE 0 
    END AS 'Check'
    FROM T1 
    INNER JOIN T2
    ON T1.PK = T2.FK
    ORDER BY T2.PK DESC
) END

GO;
ALTER TABLE Shift ADD CONSTRAINT checkAdd CHECK (dbo.CheckAddition() = 1);

answered Mar 20 '19 at 14:15

Faller

1,588
3
16
27

1

It runs, but the way you are using it will return false positives, check my answer – Daniel Brughera Mar 20 '19 at 14:26

SQL Server: Constrain Column based on Column in other table

2 Answers2