CODEWITHSUNDEEP
sql-serverconstraints
user3619502
user3619502

Reputation: 3

SQL Server Component Constraint or Constraint for more than 1 column

I want to create a table with operational details like:

CRAETED_ON DATETIME NOT NULL DEFAULT GETDATE(),
CREATED_BY VARCHAR(10) NOT NULL,
DELETED_ON DATETIME NULL,
DELETED_BY VARCHAR(10) NULL

I want to put constraint IF DELETED_ON is updated then they should provide the DELETED_BY.

BOTH should be NULL or both should not be NULL are allowed. One NULL & other NOT NULL is not allowed.

Upvotes: 0

Views: 26

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46223

This can be accomplished with a table level check constraint, assuming you want the constraint to apply to both inserts and updates:

CREATE TABLE dbo.Example(
    CREATED_ON  DATETIME NOT NULL DEFAULT GETDATE(),
    CREATED_BY  VARCHAR(10) NOT NULL,
    DELETED_ON  DATETIME NULL,
    DELETED_BY  VARCHAR(10) NULL
    ,CONSTRAINT IF_DELETED_ON CHECK ((DELETED_ON IS NULL AND DELETED_BY IS NULL) OR  (DELETED_ON IS NOT NULL AND DELETED_BY IS NOT NULL))
);

Upvotes: 3

Related Questions