jeudi 27 août 2020

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)
) 

Aucun commentaire:

Enregistrer un commentaire