Re: Check Constraint or Foreign Key Constraint

Colin Dawson wrote: > > I'm canvassing opinion on this. > > A table with millions of rows has a check constraint with 23 values in > it. I have suggested that a reference table is used instead. > > Is it better for performance to use a Check Constraint and occasionally > have maintenance if a new value is required than have easier management > of the data and a FK index. > > My preference is to use a reference table. I've never tested the comparative performance differences if any, but like you I'd prefer to see a reference table with a foreign key relationship. It documents better, everyone can see what the constraints on the values are, adds the flexibility to add new valid values on the fly without affecting production or performance, and allows the data owners to easily verify which values are actually being used. Art S. Kagel > Regards > > Colin > > There are 10 types of people in the world, those that understand binary > and those that don't > > _________________________________________________________________ > Get Hotmail, News, Sport and Entertainment from MSN on your mobile. > http://www.msn.txt4content.com/ >