SQL Server – Add Constraint to Check Value Exists in Non-Unique Composite Key Column

check-constraintsconstraintsql serversql-server-2016

I have the following table, which has a composite primary key:

CREATE TABLE dbo.Seqs(
    SequenceKey nvarchar(10) NOT NULL,
    UsageSequence smallint NOT NULL,
    col1 bit NOT NULL,
    col2 nvarchar(30) NULL,
    CONSTRAINT PK_Seqs PRIMARY KEY CLUSTERED 
    (
        SequenceKey ASC,
        UsageSequence ASC
    )
)

I'm looking to create the following table, with a 'SequenceKey' column that must only accept values that are present in the 'Seqs' table:

CREATE TABLE dbo.Sometbl (
    SomeID int IDENTITY(1,1) NOT NULL,
    Col1 int NOT NULL,
    Col2 nvarchar(100) NOT NULL,
    Col3 nvarchar(30) NULL,
    SequenceKey nvarchar(10) NOT NULL,
    CONSTRAINT PK_Sometbl PRIMARY KEY CLUSTERED (
        SomeID ASC
        )
    )

Given that the 'SequenceKey' in the 'Seqs' table is not unique (the primary key uniqueness is reliant on the 'UsageSequence' column), what is the best way to ensure data cannot be added or updated in 'Sometbl' with a 'SequenceKey' value that doesn't exist in the 'Seqs' table?

Best Answer

A solution is to use triggers to check the integrity. Remember you will have to check for operations on both tables since it's possible to delete referenced rows from dbo.Seqs.

  • First trigger on dbo.Sometbl will rollback if inserted or updated value does not exist on dbo.Seqs:

    CREATE TRIGGER dbo.utrValidateSequenceKey ON dbo.Sometbl
    AFTER INSERT, UPDATE
AS
BEGIN

    IF EXISTS (
        SELECT
            'new or updated records have an invalid SequenceKey'
        FROM
            inserted AS I
        WHERE
            NOT EXISTS (SELECT 'the sequence does not exist' FROM dbo.Seqs AS S WHERE I.SequenceKey = S.SequenceKey))
    BEGIN
        RAISERROR('Invalid SequenceKey detected on dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END

  • Then for dbo.Seqs you can rollback operation that attempts to update or delete if the SequenceKey is being referenced:

    CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnDelete ON dbo.Seqs
    AFTER DELETE
AS
BEGIN

    IF EXISTS (
        SELECT
            'deleted records have a SequenceKey being referenced'
        FROM
            deleted AS D
        WHERE
            EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
    BEGIN
        RAISERROR('Can''t delete SequenceKey being referenced in dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END

-------------------------------------------------------------------

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnUpdate ON dbo.Seqs
    AFTER UPDATE
AS
BEGIN

    IF UPDATE(SequenceKey) AND EXISTS (
        SELECT
            'updated records have a SequenceKey being referenced'
        FROM
            deleted AS D
        WHERE
            EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
    BEGIN
        RAISERROR('Can''t update SequenceKey being referenced in dbo.Sometbl', 16, 1)
        ROLLBACK
    END

END
Related Question