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 ?
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')) )
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.
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.
