a field to accept one of a set of defined values

[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?

[pritaeas]

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

[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

[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!