is there a way to enforce a set of values for a column of a database table?

I have a table in which in one of the column I would be specifying the type of entity.The entity co开发者_如何学Pythonuld only be of three types - caption , image or comment , hence I want to ensure that the table column should only accept one of these three strings . Is there a way to put this constraint in ms sql server 2008 ?

Edit :- Have added the db diagram to get tips on improving the design.

Design :- 1)A story board has a number of slides, each slide can have one or more captions or images.

2)Each storyboard can have one or more author , and zero or more reviewers.

3) Each of the objects image,slide and caption(probably more objects will come later like audio etc) can have comments on them . These comments can be given by authors or reviewers. For storing comments I have created a comment table.

4)It is necessary to remember the order of slides and order of images in a slide , for which I am using slide and image number fields.

5)Since comment can be given on any object , I needed to have a Global ID for each of the object,since they their reference will be stored in a comment table.For maintaing a global id all the fields ending with name GID are uniqueidentifiers generated using Default: NEWID().

Please suggest improvements for this db design.

---

This is how you do it with check constraints:

create table #tableWithConstrainedColumn (
    constrainThis varchar(20)
)

ALTER TABLE #tableWithConstrainedColumn ADD CONSTRAINT TempCheck
    CHECK (constrainThis in ('caption', 'image', 'comment'))

-- this statement will succeed
insert into #tableWithConstrainedColumn
select 'caption'
-- this statement will fail
insert into #tableWithConstrainedColumn
select 'captions'

drop table #tableWithConstrainedColumn

You can also define a data type, but I wouldn't recommend doing it this way: http://msdn.microsoft.com/en-us/library/aa933121(v=sql.80).aspx

As @Widor just recommended, foreign keys would be the best way to do this.

---

Sounds like a job for Foreign Keys?

---

Aha, the nature of the problem is different in that case! Hence my 2nd answer here.

I'll admit I'm not 100% sure what the 'best' solution is. I think how you plan to query this data will dictate the way you store it, to a certain extent.

From the extra info that you've posted, I'd suggest stripping back the Comment table so that it only stores information about comments. Lose the ObjID, ObjType fields and possibly usertype too - shouldn't that be in the User table?

Then, I'd suggest ading three new tables to your schema: SlideComment, CaptionComment and ImageComment, each with just two fields:
One to store the CommentId (which will have a FK to the Comment table) and another to store the Slide-, Caption- or Image- GID as appropriate.

So, these new tables are used solely to join the Comment to whichever entity they were made on. That should enable you to query the data in the way you want without having to worry which 'entity' it refers to.

---

Use check constraints or trigger BEFORE INSERT, UPDATE and code this validation into the it.