CODEWITHSUNDEEP
sql-server
J Brun
J Brun

Reputation: 1284

How can I restrict a column to certain values from another table without using a foreign key?

This is for SQL Server 2008.

I have a master lookup table that looks like this:

Mstr_lookup_ID  *Lookup_ID*   *Lookup_Category*   Lookup_Value
      1              1            States            CA
      2              2            States            NY
      3              1            Airlines          SWA
      4              2            Airlines          United
      5              3            Airlines          American

I have my primary key on Lookup_ID and Lookup_Category. I have a table that where I would like to restrict values in certain columns based upon the combination of Lookup_ID and Lookup_Category.

Create Table Main_Table 
(State_id    INT      --ONLY VALUES ALLOWED SHOULD BE 1-2
 Airline_id  INT)     --ONLY VALUES ALLOWED SHOULD BE 1-3

Is there a way to accomplish this neatly? I'd like to create a foreign key for it, but my primary key is on two columns. I'd appreciate any thoughts or suggestions.

Upvotes: 0

Views: 851

Answers (2)

marc_s
marc_s

Reputation: 754388

See Five Simple Database Design Errors You Should Avoid.

What you're trying to do is their point #1.

It sounds like a great idea - at first. Having just one table instead of many ...

But it's really not, precisely because it defeats the main purpose of a lookup tables - being able to enforce referential integrity.

Don't do this - use a separate lookup table for each category and use proper referential integrity!

The only way you could make this work would be to have a INT IDENTITY on this "global" lookup table - but that ID would be "global" across all categories. Then you could reference this global lookup table based just on that one ID. The downside is that your categories won't all have nice consecutive numbers - it'll be all over the place.

Upvotes: 1

dean
dean

Reputation: 10098

So, you have a "one big lookup table"?

To create a FK (and there is no other declarative way to restrict values based on lookup table, otherwise you can use triggers) you have to add another column to the main_table ie the lookup_category column, and draw the reference using both columns to the primary key in the lookup table.

Upvotes: 0

Related Questions