KARIM's PLAYGROUND

You know the enumeration data type, for sure. Either from programming languages (most often it's the enum keyword) or from database engines like MySQL.

What I was missing lately in my SQL Server database was the enum. To have a column that could accept only a defined set of values. As an example of MySQL enum column, is defining clothing sizes:

CREATE TABLE cloth (
    .
    size ENUM('small', 'medium', 'large')

    .
);

Unfortunately SQL Server doesn't provide this functionality out of the box and I needed to create such data type in my project when trying to define if a financial operation is an income or an outcome. Of course I could bypass this by creating an IsIncome column that stores a boolean value and later on check, if the value is false, then it means that it money where withdrawn. But what if I need to add another financial operation type? Like freezing an amount of money? I could add IsFreeze column of course, but it won't be the smartest way of designing databases.

The ideal solution for this would be creating something like MySQL provides, allowing only three values to be set in that column. We could of course create a char column and set it's value to the desired type programmatically, but again, there would be no control over the consistency of our data. Nothing prevents us from adding a random value there that is not in the set of allowed values. There is a solution to that by creating additional table, who's primary key would be of char type. We could use int type, but the problem with integers that later on the system becomes less maintainable and the data unreadable. That's why I recommend you to use a meaningful char as a key.

I'll try to reproduce the above MySQL example in SQL Server.

First of all, we should create a table that will hold our allowed values. In this case the values are the sizes:

CREATE TABLE cloth_size (
   size  char(6) NOT NULL,

   CONSTRAINT PK_Size primary key (size)

)

Next, we add three rows to our table:

INSERT INTO cloth_size ('small')
INSERT INTO cloth_size ('medium')
INSERT INTO cloth_size ('large')

Next we create the cloth table with a column named size and we reference it to the cloth_size table:

CREATE TABLE cloth (
    ...
   size CHAR(6)
    ...
   FOREIGN KEY (size) REFERENCES cloth_size(size)
);

And now we are allowed only to insert the three values that we've inserted into cloth_size table.