question

Katie 1 avatar image
Katie 1 asked

Constraint implementation

All, i want to implement a constraint on a db for table of example if i have a table like empno, empname, city ,And I have an enumerations like, dallas,austin and newyork for the city and the default being dallas. But i dont want to anything else than these three cties to be entered from the backend. . I know I could maintain a lookup table and and put a foreignkey, Second option would be creating a trigger may be? Is there any other way ee could acheive it. Like having a check constraint or something, if so will it hold multiple varchar values ?
sql-server-2008sqlconstraint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Foreign key would be the best way to do this, as you have already done an enumeration, so there's your key! You can use a check constraint create table YourTable ( empno int, empname varchar(100), city int check (city in (1,2,3,4)) --assuming these are the enums ) or without enums create table YourTable ( empno int, empname varchar(100), city varchar(50) check (city in ('dallas','austin','newyork')) )
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I just want to emphasize that foreign keys are the the technique I would use here. It is easier to add to the lookup table later than to modify the check constraint.
2 Likes 2 ·
Ditto. Why dig a hole where none is needed. Create a table with a foreign key. If you want to prevent edits to the table, make it in a different schema and give it different security or something along those lines.
1 Like 1 ·
I prefer to use check constraint instead of Trigger.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.