CODEWITHSUNDEEP
sql-serverif-statementcasecheck-constraints
Vasanth
Vasanth

Reputation: 25

SQL - Add If else condition in CHECK Constraint

I'm trying to set a condition in CHECK constraint. The scenario is

  1. When column1 is null then no action required
  2. If column1 is not null then column3 must have defined code

By example, if a student has enquired (1) then the he should have performed a action (visited as code 1 or called as code 2)

table-IMG

CREATE TABLE [dbo].[enquiry_details](
    [Id] uniqueidentifier NOT NULL,
    [Name] [varchar] (100) NOT NULL,
    [Enquired] [int] NULL,
    [location] [int] NOT NULL,
    [Action_Type] [int] NULL,
     -- CONSTRAINT menu_key CHECK ((Enquired IS NOT NULL)and Action_Type IN ('11','22'))
     --CONSTRAINT menu_key CHECK (IF(Enquired!= null)  Action_Type IN ('11','22'))
     CONSTRAINT menu_key CHECK (IF(Enquired is not null)  Action_Type IN ('11','22'))
    -- CONSTRAINT menu_keyi CHECK (CASE WHEN Enquired IS NOT NULL THEN Action_Type IN ('11','22') END)
     -- CONSTRAINT menu_keyi CHECK (CASE WHEN LEN(Enquired)>0 THEN (Action_Type '11' OR Action_Type='22') ELSE NULL END)
)

Upvotes: 0

Views: 550

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Don't think of running conditional code - just think about forming a single piece of logic:

CONSTRAINT menu_key CHECK (Enquired is null OR  Action_Type IN ('11','22'))

The first part of the OR will be false if Enquired is not null, in which case only the second part of the OR can satisfy the overall condition.

Upvotes: 1

Related Questions