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.





Last Modified: 19 February 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-29 - MSSQL - TRIM Function