Referential integrity with composite primary keys?

[Raymas]

Hi all,

I'm creating a database and I'm having trouble with composite primary keys. I've read on a few forums that good databases should use composite primary keys over say an autonumber. And it does neaten up the database a bit, i.e. if a record is deleted there is no messy skipped numbers in the primary key (ID field). A simple example of my problem from my database is when I have two tables one that contains a list of raw materials and its relationship with the bill of materials (materials matched with products, a many-many table).

tbl_Raw_Materials
Material_Category
Material_Name (Primary Key)
Material_Colour (Primary Key)

tbl_Bill_of_Materials
Product_Code (Primary Key)
Material_Name (Primary Key)
Material_Colour (Primary Key)
Material_Length (Primary Key)
Material_Quantity

I have chosen these composite keys such that each record will contain a unique primary key. However, I have yet to work out how to induce referential integrity in these instances due the primary keys, and not recieve the message,

"No unique index found for the referenced field of the primary table."

Is there a way to achieve referential integrity while using composite primary keys? My main reason is to insure when a material is deleted from the database that it follows through to the bill of materials.

Cheers,

Raymas

 

[Galaxiom]

I've read on a few forums that good databases should use composite primary keys over say an autonumber.

There are pros and cons for every decision. Composite keys can be a good idea in some situations particularly if the fields are altredy indexed.

And it does neaten up the database a bit, i.e. if a record is deleted there is no messy skipped numbers in the primary key (ID field).

That is not a good reason to use a composite key. The key is there for the machine not the user. The computer does not really care about the aesthetics of unbroken sequences.

The keys you have indicated in your tables are not good candidates if they are text. Text fields are relatively slow to join. If you used Integer codes for MaterialCode and MaterialColour in the RawMaterial table then it could be suitable.

More than two fields as a key starts to get messy. They must all appear everywhere the foreign key is required. I would definitely not use four.

I would use a synthetic key on the BillOfMaterials table but only if there are more tables related to it. Note that despite common belief, it is not essential to have a key on every table and there are good reasons not to include one in some cases.

Rather than making these fields the key, you can simply include them as a composite index (No Duplicates). This will prevent the combination being added a second time without the clutter of having to use the whole group as a key.

AFAIK There is no way to enforce referential integrity on composite keys.