Constraint implementation


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 ?

more ▼

asked Jan 03, 2012 at 08:41 AM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jan 03, 2012 at 08:48 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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.

Jan 03, 2012 at 09:16 AM TimothyAWiseman

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.

Jan 03, 2012 at 10:15 AM Grant Fritchey ♦♦

I prefer to use check constraint instead of Trigger.

Jan 03, 2012 at 09:40 PM Sharma
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 03, 2012 at 08:41 AM

Seen: 908 times

Last Updated: Jan 03, 2012 at 08:43 AM

Copyright 2018 Redgate Software. Privacy Policy