Some database engines support enum. Some database engines do not. Or do they? If you have ever tried to create a table with a field with an enum you will know that it is not possible. However it is possible to simulate the same functionality using a constraint.

Here is a quick chunk of code to demonstrate how this is possible. What we are aiming for is to make sure that on a contact table a gender field is either populated with M, F, U (male / female / unknown).

Lets create a customer table with the following.

CREATE TABLE [dbo].[Customer](
        [FirstName] [nvarchar](64) NULL,
        [LastName] [nvarchar](64) NULL,
        [Gender] [varchar](1) NULL,
) ON [PRIMARY]
GO

Now that we have a table we can change it slightly so our constraint will work.

ALTER TABLE [dbo].[Customer]
        WITH CHECK ADD  CONSTRAINT [CK_CustomerGender]
                CHECK  (([Gender]='U' OR [Gender]='F' OR [Gender]='M'))
GO

Now we have the constraint we will be able to test it using the following. First of all lets insert a row which we know is valid

INSERT INTO Customer (FirstName, LastName, Gender)
        VALUES('fjskafs', 'fsvxxvbc', 'M')

Now lets try an invalid value

INSERT INTO Customer (FirstName, LastName, Gender)
        VALUES('fjskafs', 'fsvxxvbc', 'A')

We get the following error message

The INSERT statement conflicted with the CHECK constraint "CK_Customer". The conflict occurred in database "Mess", table "dbo.Customer", column 'Gender'

Now the table has the same functionality as an enum.