2

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?

1

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
    
1

As a possible answer to my own question, this could be achieved by adding a Check constraint with a function such as the following:

CREATE FUNCTION dbo.CheckSequenceKey (@SequenceKey nvarchar(10))
RETURNS bit
AS 
BEGIN
  DECLARE @retval bit

  IF EXISTS (SELECT 1 FROM dbo.Seqs S WHERE S.SequenceKey = @SequenceKey)
      SET @retval = 1
  ELSE 
      SET @retval = 0

  RETURN @retval
END;
GO

ALTER TABLE dbo.Sometbl
  ADD CONSTRAINT chkSequenceKey
  CHECK (dbo.CheckSequenceKey(SequenceKey) = 1);

EDIT:

The Check constraint prevents inserts and updates to the 'Sometbl' table where the new value for 'SequenceKey' is not found within the 'Seqs' table and this is my preference over the use of a trigger.

However, the check constraint doesn't restrict updates to values in the 'SequenceKey' column in the 'Seqs' table or the deletion of entire rows from this table, which could lead to orphaned records in the 'Sometbl' table. Therefore I've taken the below from EzLo's answer to be applied in addition to the check contraint and function for a more complete solution:

  • 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
    
2
  • My only advice would be to make it a "SELECT TOP (1) 1". – Jonathan Fite Mar 19 '19 at 12:49
  • 3
    Using a CHECK will only validate the row on insertion, won't prevent the Seqs record from being deleted or updated. – EzLo Mar 19 '19 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.