Foreign key constraints

[pto]

I am very new to sql. I have a programmer who has a database with a table that has 1 primary key and 4 foreign keys. He wants to make sure that if a field is deleted that an error comes up. However, I was under the impression that a table should only have one primary key and one foreign key. Is this right? I would appreciate any feedback.

 

[crystalized]

Actually in my experience it is not uncommon to have a table that has multiple foriegn keys in it - when you normalize your database often you are representing entities that are composed of a number of pieces. Often the pieces should be represented in separate tables so as to avoid duplication. In this case the table representing the whole would have foriegn key fields for each of the pieces.

So if you are trying to represent a computer you might need to keep track of pieces like processors, motherboards, cases etc.

You could do the following
Computer(ID,processorname,processorspeed,processorpins, motherboardname,motherboardmaker,.........casecolor)

All in one table but you would be duplicating information what you probably want is something more like this

Processor(ID,name,speed,pins..........)
Motherboard(ID,name,maker,...........)
Case(ID,color........)
Computer(ID,processorID,motherboardID,caseID)

Then when you want the complete info about the computer you join the tables computer,processor,motherboard, and case and bring all the information together.


I hope this helps a little bit Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[pto]

Thanks for the assistance.