1,105,625 Community Members

a field to accept one of a set of defined values

Member Avatar
dinhunzvi
Junior Poster in Training
98 posts since Jul 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 7 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
pritaeas
mod_pritaeas
11,315 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

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
G_Waddell
Practically a Posting Shark
821 posts since Nov 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 137 [?]
Skill Endorsements: 13 [?]
 
0
 

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
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
1
 

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!

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: