Re: Check Constraint vs. Foreign Key Constraint

J Lowry Snow wrote:
>
> My question has to do with (1)which method is faster and (2)which
> method better adheres to good database design.
>
> This is my dilemma. I can't decide which method is better. For
> example, in the Vendors table, I can have either 4 or 5 CHECK
> constraints or I can create 4 or 5 tables and use Foreign Key
> relationships to restrict data entry. Each vendor is either a
> "Manufacturer" or a "Distributor". For the field VendorType, would it
> be better to write a check constraint ensuring the type to be entered
> as "Manufacturer" or "Distributor" or should I create a new table
> called VendorTypes and add two columns, TypeID and TypeDescription,
> and check the field using a Foreign Key relationship?
>
> What are your thoughts on the CHECK CONSTRAINT versus FOREIGN KEY
> entity relationship?

I'd say it depends on whether you can (perhaps only vaguely) imagine that the vendor types will change sometime in the future. You're definitely more flexible when going for a foreign key relationship since a change in the allowed types doesn't involve any DDL.

Furthermore, you can use some kind of shortened key (e.g. "M"/"D") and still have the full description at hand. And perhaps you'll need more information for the types in the future.

hth,
Heinz Received on Tue Aug 07 2001 - 07:59:19 CEST