How to validate a column using stored procedure

[rahulae]

I have a column name Room_type ,the valid room types are bedroom,dining room,living room,kitchen,bathroom.

I have to validate the room_type column such that only valid room types are allowed.

[code green]

The problem is that invalid data has been entered.I am assuming at the front end.
This can be rendered impossible by this design

I would have a seperate room_types table
room_types
type_id   description
1            bedroom
2            dining
etc
Then your Room_type column can only contain a valid id.

You can easily migrate to this design and in the process filter out the invalid data.
Or maybe not. Let me know

[ck9663]

Then create a relationship (FK - to - PK) between the two table.

-- CK

[rahulae]

its not like that i have to write a procedure where in i should be able to insert a room and while inserting i should see that room type is valid.

valid types i have listed already.

I cannot use one more table

[ck9663]

It's better if you create a CONSTRAINT.

If some inserted a row on your table without using your SP, it might still have invalid values.

-- CK

[rahulae]

can you tell me how to create constraint for that because the valid room types are around 4

[rahulae]

Is there any way to validate that column without using different table and constraint

[ck9663]

Here are some readings.

-- CK

[rahulae]

How does that concept of constraints help solve my problem

see my problem is whenever i try to insert a room_type into a table it shouldn't allow any other types other than that i have listed.

what can i do to solve this.

will using a constraint solve this out.

if yes what type of constraint and give me an example

[ck9663]

A CHECK CONSTRAINT can be created for a field to ensure that the condition has to be fulfilled first before SQL SERVER allows insert/update on the table. So if you have a constraint on your room_type column that says all valid values should be IN ('BEDROOM','DINNING'), then only these two values will be inserted on your column, otherwise it will not allow the insert or update of the entire record.

-- CK

[rahulae]

plz tell me how to write it

i mean syntax

[rahulae]

how to write a check constraint expression to solve my problem