How to make the default selection of a value in sql out of multiple choices?-CodePudding

I have a column in my SQL table i.e. Gender and there can be one of two possible values for it, 'M' and 'F'.

For those values, I am able to pass two values by using a check constraint as option when creating the table:

Gender varchar(6) CHECK (Gender IN ('M', 'F'))

Also, one of those value is defined as the default:

Gender varchar(6) DEFAULT 'M'

But here, if I am trying to merge those two queries while table creation, I am not getting the output. I want to pass two choices for column value and default as 'M'.

CodePudding user response：

Both can be used as part of the create table syntax:

create table t(gender char(1) default('M') check(gender in ('M','F')));

Demo Fiddle

CodePudding user response：

You can easily use both in the CREATE TABLE statement - and preferably, define explicit names for your constraints!

CREATE TABLE Person
(
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Gender CHAR(1) NOT NULL
        CONSTRAINT CHK_Person_Gender CHECK (Gender IN ('M', 'F'))
        CONSTRAINT DF_Person_Gender DEFAULT ('M')
)