Re: Check Constraint vs. Foreign Key Constraint

In article <be820bbe.0108060918.71f3c250_at_posting.google.com>, J Lowry Snow says...
>
>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?
>

The theory would probably insist on puting only the predicates that cannot be expressed as a finite set relationships into CHECK CONSTRAINT. For example: age < 130.

As comparing the performance, foreign key constraint is defined on logical level. Therefore, there is no reason why it could't be as fast as CHECK CONSTRAINT, but, I'm afraid, on practice it would. Received on Mon Aug 06 2001 - 22:22:06 CEST