CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008types
Patrick
Patrick

Reputation: 5592

SQL Server equivalent to MySQL enum data type?

Does SQL Server 2008 have a a data-type like MySQL's enum?

Upvotes: 188

Views: 209155

Answers (6)

carloswm85
carloswm85

Reputation: 2382

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)

sql server enum implementation

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:

values for enum table

  • 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.Role different than those available at the Role table, you'll get an error.

enter image description here

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

Upvotes: 1

Jonan87
Jonan87

Reputation: 51

You can try something like

ALTER TABLE dbo.yourTable
ADD CONSTRAINT yourColumn CHECK(yourColumn IN('XL','L','M','S','XS'))

Upvotes: 2

Jony Adamit
Jony Adamit

Reputation: 3416

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!

Upvotes: 8

user1431422
user1431422

Reputation: 1139

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.

Upvotes: 113

Dimitrios Staikos
Dimitrios Staikos

Reputation: 333

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.

Upvotes: 5

chaos
chaos

Reputation: 124297

It doesn't. There's a vague equivalent:

mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))

Upvotes: 219

Related Questions