Member Avatar for dinhunzvi

i have a problem. i have a table that has one column that should accept one of a set of defined values. i know in MySQL i use the following command to create the table:

CREATE TABLE tblstudents (
    studentID int( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT,
    lastName VARCHAR( 20 ) NOT NULL,
    initial CHAR( 1 ),
    firstName VARCHAR( 20 ) NOT NULL,
    gender ENUM( 'Male', 'Female' ) NOT NULL,
    PRIMARY KEY studentID ( studentID )
   )

what command do i use in SQL Server?

  • 4 Contributors
  • 3 Replies
  • 230 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

SQL Server does not have a MySQL-style ENUM. Three practical patterns appear in the thread: a lookup table + foreign key (as suggested), a CHECK constraint (as showed), or a BIT/boolean for two-state data (as mentioned). Each is valid; choice depends on flexibility, normalization and maintenance needs.

A normalized lookup table is the most robust and future-proof option — it stores a small code rather than repeated strings, makes labels editable without ALTERing the parent table, and allows extra metadata (sort order, display text, locales). Example schema sketch:

CREATE TABLE GenderLookup (
  GenderID TINYINT PRIMARY KEY,
  GenderName VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO GenderLookup (GenderID, GenderName)
VALUES (1, 'Male'), (2, 'Female'), (3, 'Non-binary');

CREATE TABLE Students (
  StudentID INT IDENTITY(1,1) PRIMARY KEY,
  LastName VARCHAR(20) NOT NULL,
  Initial CHAR(1),
  FirstName VARCHAR(20) NOT NULL,
  GenderID TINYINT NOT NULL REFERENCES GenderLookup(GenderID)
);

A CHECK constraint is simplest for a tiny, fixed list and avoids a join, but it’s fragile if values must change later and depends on collation for case sensitivity (default SQL Server collations are case‑insensitive, so 'male' == 'Male'). A BIT column is compact for true/false, but has poor semantics for things like gender and hurts clarity unless the meaning is obvious.

Also note: MySQL ENUM maps values to internal ordinals, which can cause surprises when migrating — a lookup table preserves semantics and portability. For most production schemas where the allowed set may evolve or needs to carry metadata, prefer the lookup-table + FK; use CHECK for truly fixed lists and BIT only for genuine binary flags.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

AFAIK MSSQL does not support ENUM's. What you can do is create a table for the values, and use a FK instead.

Member Avatar for G_Waddell

Or you could use a bit field (True/ False) - but it would have to make sense as it would only return a true (1) or false (0)

OR
If you pass a bit parameter you could do something like this in a strored procedure:

IF(@Male =1) 
BEGIN 
    UPDATE tblStudents Set gender ='Male' WHERE (studentID = @ID) 
END 
ELSE 
BEGIN 
    UPDATE tblStudents Set gender ='Female' WHERE (studentID =@ID)
END
Member Avatar for BitBlt

Actually, there is an object in SQL Server called a Check Constraint. You can use it to put all kinds of restrictions on columns. Here's what you would use:

CREATE TABLE tblstudents (
    studentID int identity(1, 1) NOT NULL primary key,
    lastName VARCHAR( 20 ) NOT NULL,
    initial CHAR( 1 ),
    firstName VARCHAR( 20 ) NOT NULL,
    gender varchar(6) CHECK (gender in ('Male', 'Female') ) NOT NULL

   )

Here's some test inserts to demonstrate how it would work:

   insert into tblstudents
   (lastName, initial, firstName, gender)
   values
   ('Smith', 'J', 'Fred', 'male')
--works fine
   insert into tblstudents
   (lastName, initial, firstName, gender)
   values
   ('Smith', 'J', 'Cindy', 'female')
--works fine
   insert into tblstudents
   (lastName, initial, firstName, gender)
   values
   ('Smith', 'J', 'Androgyne', 'other')
--gives the following error:
--Msg 547, Level 16, State 0, Line 1
--The INSERT statement conflicted with the CHECK constraint "CK__tblstuden__gende__6AEFE058". The conflict occurred in database "TestingStuff", table "dbo.tblstudents", column 'gender'.
--The statement has been terminated.

Hope this works for you. Good luck!

Edited by BitBlt
commented: Thanks for the correction. +13
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.