In SQL Server (or it could be other SQL language I guess), how do you make sure that a table always verify some complex constraint. For instance, imagine a table has only column A and B

The constraint I want to enforce, is that all lines that contains the same value for column A (eg 'a'), also have the same value for column B (eg 'b'). For instance, this isn't allowed:

A B C
a1 b c1
a1 c c2
a2 d c3

But this is allowed:

A B C
a1 b c1
a1 b c2
a2 d c3

This is an example of constraint, but there could be more complex (ie depends on more columns etc). Here I think that this constraint could be expressed in terms of primary key etc, but let's assume it can't

What is the way to enforce this?

What I can think about is, each time I want to do an insert, I instead go through a stored procedure. The stored procedure would do an atomic transaction and check if the table would verify the constraint if updated, and "reject" the update if the constraint would be violated. But it seems quite complex

CodePudding user response：

• SQL Server supports UDFs in CHECK constraints.
• While CHECK constraints are ostensibly a row-level-only constraint, a CHECK constraint can still use a UDF which checks the entire table, and I believe that then suits your purposes.
  • Note that CHECK constraints are evaluated for every row in a table (though only on-demand) and SQL Server isn't smart enough to detect redundant constraint evaluations. So be careful - and be sure to monitor and profile your database's performance.

(The inner-query in the function below returns the A and COUNT(DISTINCT x.B) values so you can easily copy paste the inner-query into a new SSMS tab to see the invalid data. Whereas the function's actual execution-plan will optimize-away those columns because they aren't used by the outer-query, so there's no harm in having them in the inner-query).

CREATE FUNCTION dbo.GetCountInvalidGroupsInTableX()
RETURNS bit
AS
BEGIN

    DECLARE @countInvalidGroups int = (
        SELECT
            COUNT(*)
        FROM
            (
                SELECT
                    x.A,
                    COUNT(DISTINCT x.B) AS CountDistinctXB
                FROM
                    dbo.TableX AS x
                GROUP BY
                    x.A
                HAVING
                    COUNT(DISTINCT x.B) >= 2
            );
   
    RETURN @countInvalidGroups;

END

Used like so:

CREATE TABLE TableX (
    A int NOT NULL,
    B int NOT NULL,

    CONSTRAINT PK_TableX PRIMARY KEY ( etc )
);

CREATE FUNCTION dbo.GetCountInvalidGroupsInTableX() RETURNS bit
AS
    /* ... */
END;

ALTER TABLE TableX
    ADD CHECK CONSTRAINT CK_Validity CHECK ( dbo.GetCountInvalidGroupsInTableX() = 0 );