MSSQL - Enum

26. May 2011 18:38

 

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 possbile. 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 then table has the same functionality as an enum.

E-mail Kick it! DZone it! del.icio.us Permalink