locked
Adding a default constraint RRS feed

  • Question

  • How do I add multiple default constraints to a column. for example:

    Table Name : AcknowledgedType

                                    Columns:    AcknowledgedTypeID  [ Possible values  are 1 or 2] 

                                                           Name [ Possible Values  are Automatic or Manual]

    Thursday, October 27, 2016 2:22 AM

Answers

  • --Create table AcknowledgedType ( AcknowledgedTypeID  int,Name varchar(30))
    
    
    alter table AcknowledgedType
    add  CONSTRAINT CK_AcknowledgedTypeID_values CHECK (AcknowledgedTypeID IN (1,2)) 
    alter table AcknowledgedType
    add  CONSTRAINT CK_name_values CHECK (name IN ('Automatic','Manual')) 
    
    --insert into AcknowledgedType values(1,'Automatic'),(2,'Manual')
    
    
    
    
    --select * from AcknowledgedType
    --drop table AcknowledgedType

    • Marked as answer by dbSlave65 Thursday, October 27, 2016 3:33 AM
    Thursday, October 27, 2016 2:41 AM
  • you would need to do it in 2 steps

    alter table AcknowledgedType ADD CONSTRAINT CK_AcknowledgedType CHECK (AcknowledgedTypeID between 1 and 2)

    alter table AcknowledgedType ADD CONSTRAINT CK_Name CHECK (Name in ('Automatic','Manual'))

    • Marked as answer by dbSlave65 Thursday, October 27, 2016 3:33 AM
    Thursday, October 27, 2016 2:46 AM

All replies

  • --Create table AcknowledgedType ( AcknowledgedTypeID  int,Name varchar(30))
    
    
    alter table AcknowledgedType
    add  CONSTRAINT CK_AcknowledgedTypeID_values CHECK (AcknowledgedTypeID IN (1,2)) 
    alter table AcknowledgedType
    add  CONSTRAINT CK_name_values CHECK (name IN ('Automatic','Manual')) 
    
    --insert into AcknowledgedType values(1,'Automatic'),(2,'Manual')
    
    
    
    
    --select * from AcknowledgedType
    --drop table AcknowledgedType

    • Marked as answer by dbSlave65 Thursday, October 27, 2016 3:33 AM
    Thursday, October 27, 2016 2:41 AM
  • you would need to do it in 2 steps

    alter table AcknowledgedType ADD CONSTRAINT CK_AcknowledgedType CHECK (AcknowledgedTypeID between 1 and 2)

    alter table AcknowledgedType ADD CONSTRAINT CK_Name CHECK (Name in ('Automatic','Manual'))

    • Marked as answer by dbSlave65 Thursday, October 27, 2016 3:33 AM
    Thursday, October 27, 2016 2:46 AM