Complicated SQL constraint-CodePudding

I want to prevent data being written using a constraint, but the constraints are complicated.

I have 3 columns: A B C

A record is duplicate if A & B match an existing record, and if A & C match an existing record, but it's valid for B & C to match an existing record, unless A=2.

E.g. Assume existing records

A=1,B=2,C=3

If I insert

A=1,B=2,C=4

I should get an error

If I insert

A=1,B=99,C=3

I should get an error

If I insert:

A=99,B=2,C=3

That will succeed.

If I insert:

A=2,B=2,C=3

I should get an error.

The checks have to be done at INSERT time. I can't SELECT then INSERT because the data may have changed between the SELECT and the INSERT.

Ideas:

If I put the logic in a TRIGGER would that work? I'm not sure if triggers are 100% atomic.
Would a TRANSACTION work? I don't want to LOCK the table because other processes will be inserting data constantly.
Could I add one or more constraint(s)? Is there a way to check the constraints at INSERT time?

CodePudding user response：

For the simple combinations of uniqueness for columns A & B and A & C you can solve this with a unique index or unique constraint. For the latter problem, however, this isn't so easy.

I use a TRIGGER here, and check to see if a matching row, with the same values of B & C is found (where the ID differs, as otherwise the row will match to itself) and where either the inserted row has a value of 2 for A or the existing row does. If a match is found, an error is THROWn, causing the INSERT/UPDATE to fail.

CREATE TABLE dbo.YourTable (ID int IDENTITY,
                            A int NOT NULL,
                            B int NOT NULL,
                            C int NOT NULL);
GO

CREATE UNIQUE INDEX UX_YourTable_AB ON dbo.YourTable (A,B);
GO
CREATE UNIQUE INDEX UX_YourTable_AC ON dbo.YourTable (A,C);
GO

CREATE TRIGGER trg_chk_YourTable_BC_A2 ON dbo.YourTable
AFTER INSERT, UPDATE AS
BEGIN

    IF EXISTS (SELECT 1
               FROM inserted i
                    JOIN dbo.YourTable YT ON i.B = YT.B
                                         AND i.C = YT.C
                                         AND i.ID != YT.ID
                WHERE 2 IN (i.A,YT.A))
        THROW 54697, N'The check trigger ''trg_chk_YourTable_BC_A2'' on table ''dbo.YourTable'' failed. Cannot insert duplicate row on columns ''B'' and ''C'' where at least one row has the value of ''2''.',16;
END;
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(1,2,3);
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(1,2,4); --Fails
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(1,99,3); --Fails
GO
INSERT INTO dbo.YourTable (A,B,C)
VALUES(99,2,3); --Works
GO
INSERT INTO dbo.YourTable (A,B,C)
VALUES(2,2,3); --Fails
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(4,5,6); --Works

GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(5,5,6); --Works
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(2,5,6); --Fails
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(3,7,9); --Works
GO

INSERT INTO dbo.YourTable (A,B,C)
VALUES(2,7,9); --Fails

GO
SELECT *
FROM dbo.YourTable;

GO
DROP TABLE dbo.YourTable;

Obviously, you may well wish to change the error number and message, but I wrote something reasonable. db<>fiddle

CodePudding user response：

At a glance, I think you can use unique filtered indexes.

UNIQUE : A,B --always
UNIQUE : A,C --always
UNIQUE B,C WHERE A==2

CodePudding user response：

I would try using Common Table Expressions and add a where clause on the insert statement. If the insert returned zero rows updated, then consider the insert failed?

-- Assuming A and B do not permit NULLs
; WITH cteAB AS (
    SELECT DISTINCT a,b FROM YourTable
)
, cteBC AS (
    SELECT DISTINCT b,c FROM YourTable
)
INSERT  YourTable ( a, b )
SELECT NewA, NewB
FROM YourTable yt 
LEFT JOIN cteAB ab ON ab.a=NewA AND ab.b=NewB
LEFT JOIN cteBC bc ON bc.a=NewA AND bc.b=NewB
WHERE ( ab.a IS NULL AND bc.a IS NULL )
    OR NewA=2