SQL Multiple constraints on a single column-CodePudding

I am working with sql server and the table of workers.
I need to add a column "Gender" and apply two constraints : check and default.

So i need to check that they gender is male/female and the default value should be "To be updated"

My query looks like that by it doesn't work:

ALTER TABLE Workers
ADD Gender VARCHAR(6) CHECK (Gender IN ('Male', 'Female'))
           DEFAULT 'To be updated'

CodePudding user response：

You don't say what "doesn't work" means, however varchar(6) doesn't accomodate the 13 characters of 'To be updated', and you need to include your default value as an allowable value. It's also a good idea to specifically name the constraints which enables you to easily reference them in future.

alter table Workers add 
  Gender varchar(13) 
    constraint GenderCheck check (Gender in ('Male', 'Female','To be updated') ) 
    constraint GenderDefault default ('To be updated');

Working Demo

Also note - specifying not null when adding the column will apply the default to existing rows.