April 23, 2009 at 1:18 pm
We have many of tables that store a unit of measure for various readings. For instance lbs, kg, g, oz
Now I can put a check constraint on each column in each table but if I need to add to that, that would require changing each constraint.
We have a UDT called UOM of varchar 3, for consistancy purpose, but I can not find if we can put a check constraint on a UDT.
Would it just be better to assign these values an integer, for joining purposes, then link to a look up table and create multiple foreign keys to the look up table to enforce valid values?
Thank you
April 23, 2009 at 1:38 pm
You can't place a check constraint directly on a UDT. (That's actually come up twice this afternoon on this site.)
You can create a table of valid values and use FKs to that, but it can just as easily be a 3-character string as a number. Doesn't have to be a number. Can be if you like, but there's no reason for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 2:10 pm
It sounds like you have quite a few of these columns, so I'd have to concur that using a FK to a new table of those acceptable values would be the best solution for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2009 at 2:23 pm
If you decide to go with a table - then using a 3 character mnemonic as the key value would be my approach. That way, you have the mnemonic value in the table for most uses and access to expanded information when needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply