Does SQL Server 2008 have a a data-type like MySQL's enum?
-
Found this interesting approach when I wanted to implement enums in SQL Server. The approach mentioned below in the link is quite compelling, considering all your database enum needs could be satisfied with 2 central tables. SQL SERVER – Enumerations in Relational Database – Best Practiceuser_v– user_v2013-08-27 08:07:30 +00:00Commented Aug 27, 2013 at 8:07
-
1@user_v This is a variation of the anti-pattern known as "one true (lookup) table". The proper approach is to have separate table for each enum type and use foreign keys (if you need lookup at all, which may not be the case for "pure" enums).Branko Dimitrijevic– Branko Dimitrijevic2014-09-04 13:22:41 +00:00Commented Sep 4, 2014 at 13:22
-
@user_v The comments on the linked page provide good backup for using individual tables for each "enum", rather than what this answer specifiesnikodaemus– nikodaemus2015-04-13 20:14:06 +00:00Commented Apr 13, 2015 at 20:14
-
stackoverflow.com/questions/3419847/what-is-a-lookup-table Seems to be the solution for this, as suggested by some answers here.carloswm85– carloswm852022-10-25 17:10:38 +00:00Commented Oct 25, 2022 at 17:10
Add a comment
|
6 Answers
It doesn't. There's a vague equivalent:
mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))
16 Comments
Elaskanator
Why not use a table that defines valid values, and then use a foreign key constraint instead?
userfuser
@Elaskanator I'd say this most precisely answers the direct OP, while the best solution for it is probably the FK+external table usage.
Andrew
Thank you @Elaskanator for reminding me of the obvious... normalize the data and enum's stop existing.
Dai
@Elaskanator Often enum values are defined in application code first (e.g. C#
enum), whereas if they used a table FK reference then those supposedly static enum entries could be modified at runtime which would be undesirable (and SQL Server doesn't support the concept of immutable tables), finally if you have lots of enums with only a few values then you'll end-up adding lots of tables to your database. Not to mention extra IO reads due to FK constraint-checking when inserting/deleting data, whereas a CHECK CONSTRAINT is much faster and doesn't cause database object spam.
James O'Brien
I agree with @dai. I used to use code-tables, but I see them as overkill. I like the CHECK functionality, and varchar. It allows you to use a static Enum in C# without a bunch of fancy workarounds. Performance-wise, sqlserver is perfectly capable of indexing strings almost as well as ints or smallints. The performance gain is minimal compared to the additional coding complexity. Time is money.
|
The best solution I've found in this is to create a lookup table with the possible values as a primary key, and create a foreign key to the lookup table.
15 Comments
HLGEM
A better solution from a maintainability perspective than the check constraint shown above.
ypercubeᵀᴹ
This is a better solution than Enums - in MySQL as well.
BenR
@ypercube Why is it better for MySQL as well?
ypercubeᵀᴹ
@BenRecord There are several issues with MySQL's enums: 8 Reasons Why MySQL's ENUM Data Type Is Evil. I don't agree 100% that it's evil but you have to be extra careful when using them.
Jim Schubert
@BenR also if I recall correctly, in non-strict mode with MySQL, an invalid enum can be inserted as a NULL. Whatever the condition, a previous team of mine had issues with a MySQL enum not getting inserted and not failing when the value wasn't specified in the list of values. A foreign key constraint on a lookup table would cause a failure. I agree the lookup table is better for MySQL.
|
IMHO Lookup tables is the way to go, with referential integrity. But only if you avoid "Evil Magic Numbers" by following an example such as this one: Generate enum from a database lookup table using T4
Have Fun!
3 Comments
Mark Schultheiss
You don't need T4 to do this...
Jony Adamit
Haha @MarkSchultheiss you DID notice this was 10 years ago right??
Mark Schultheiss
Yes, just adding a comment for that - not suggesting this was a bad answer or even still is in some places. Sometimes technology changes, imagine that!
CREATE FUNCTION ActionState_Preassigned()
RETURNS tinyint
AS
BEGIN
RETURN 0
END
GO
CREATE FUNCTION ActionState_Unassigned()
RETURNS tinyint
AS
BEGIN
RETURN 1
END
-- etc...
Where performance matters, still use the hard values.
Comments
You can try something like
ALTER TABLE dbo.yourTable
ADD CONSTRAINT yourColumn CHECK(yourColumn IN('XL','L','M','S','XS'))
Comments
Probably the best solution for this is a simple look-up table (What is a lookup table?). Nevertheless you can implement something like this:
Solution
SQL Server Management Studio (SSMS)
User.Role is the Foreign Key here, and Role.Type is the Primary Key it refers. And in that table, you'll have the following values:
- The type of which must match in both tables. In this case that type is:
nvarchar(15) - If you try to add a value at
User.Roledifferent than those available at theRoletable, you'll get an error.
SQL Code
CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](50) NOT NULL,
[Email] [nvarchar](75) NOT NULL,
[Password] [nvarchar](25) NOT NULL,
[Role] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Role] FOREIGN KEY([Role])
REFERENCES [dbo].[Role] ([Type])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role]
GO
CREATE TABLE [dbo].[Role](
[Type] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Solution 2
You can implement a lookup table like this one: Create enum in SQL Server
