Link to home
Start Free TrialLog in
Avatar of Babak Sekandari
Babak SekandariFlag for United States of America

asked on

Using Reference Tables to Ensure Value Restrictions

In a SQL Server database I have some reference tables to limit possible choices in a column.
For example, in a table called, Common, I have a column called, Exemption, which can only have Exempt or Non-Exempt as the possible values, so I created a table called, FLSA with one column called Exemption and I populated it with those two values. That one column is the primary key.
In another instance, I had a column called Degree for the minimum degree requirement for a position. I'm going to create a table called, Degrees, with one column called Degree which I will populate with Associates, Bachelors, Masters, PhD. Again, that one column is the primary key.
My questions are:
1. Do I need a foreign key relationship between the Common table Exemption column and the FLSA table Exemption column?  
I don't see why I would need any sort of relationship between those columns; but I want the business rules to be enforced on the data base side as much as possible instead of on the application side so that if any other future programmer (including a future me who's forgotten the business rules) tries to create another app from this data base or alter an existing app.
2. Even if I did have a foreign key relationship between Common Exemption and FLSA Exemption, that wouldn't really stop someone from putting an errant value into the Common table Exemption column, would it?
3. If I relationship between the main table and the reference table isn't the correct way to enforce the restriction of values, then what would be an effective and easy way? All I can think of is to create a trigger that checks to make sure that the correct table has been referenced. It would be an "INSTEAD OF" DML Trigger that would fire for an INSERT statement and override the INSERT and roll back the statement with an error when the INSERT statement goes against the stated business policy. Is there an easier way? A more straightforward way that accomplishes the mission?
4. If I do use a trigger, is there any reason why I would still need a foreign-key relationship between the original table and the reference table? Note, another reason I want the reference table is so that when an application wants to populate the relevant drop-down-list, it will use a consistent source instead of hard-coding it into each application.

Edit:
I just thought of another possibility:
5. What if I combined a reference table with a check constraint? Could I do something like this:
check ( Degree in (SELECT Degree FROM Degrees))
But then, that doesn't provide an error message ability.
With a trigger, I could include something like:
AS RAISERROR ('The values must come from the Degrees table.');
ROLLBACK
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Babak Sekandari

ASKER

The main table's column would hold the primary key of the reference table. Is that right?
I think that I should do this sort of reference table even if I have values as simple as True and False. After all, I might have a time in the future when the business rules want to add other choices like, Unknown, To Be Determined, etc. Do you agree?

Does the active/inactive column have its own reference table?

Edit:
I just found the answer to the True/False question:
bit (Transact-SQL)

Then I can have a calculated column to convert the 0 to false and 1 to true, I suppose.
Generally, displaying true/false or yes/no is left up to the application. A bit field is the proper place to store this information, though.

Active/Inactive would be a bit field. Its purpose is to tell the application whether that reference value should be used when adding / updating data.
The statement, "Using a foreign key will prevent values being entered into your main table's column that are not in the reference table," was correct.
I tested it. I created a reference table and made a foreign key relation to it. Then I tried to INSERT into the main table with a value that was not in the reference table ID column, and that INSERT statement was rejected, just as I wanted.
Thanks.
That was easy. I wonder why so many sites recommend a CONSTRAINT CHECK:
Restrict varchar()
Limit SQL Server column to a list of possible values
limit sql server column to a list of values
As mentioned in the last link you provided, using a check constraint can be used, but using a reference table allows the application to also display the list of valid values when adding/updated records.

The other downside of the check constraint is that it is built into the database design. If you needed to add a valid value, you would need to update the design which could require system down time, depending on policies/procedures for modifying databases. Adding a valid value to the reference table is just an insert and should not require the down time.