Re: Compound PK or ?

>> First, is there any general consensus about whether or not compound
primary keys are good or bad and in which situations? <<

That statement makes no sense. A key is –- by definition -– a subset of the attributes of an entity that uniquely identify that entity. You discover them in the reality that you are modeling; you do not play god and crate the universe. Failure to put a UNIQUE constraint on a real key means your model and the reality do not match.

If you use longitude and latitude to locate a place that is a fact in the real world, not a choice you can change on your own whim.

>> Second, if I have a natural candidate primary key that is two
attributes (compound), but I create an additional/superfluous single attribute to function as a PRIMARY KEY, is there a name for this type of key/attribute? (Such an attribute has no actual meaning externally)...<<

I did an article on this kind of thing in INTELLIGENT ENTERPRISE on the website. If the new column (it is not an attribute) is created by the system and hidden from the user, then it is a surrogate key. Example: my SQL hashes (longitude, latitude) in a special index I don't know about.

I also discussed things like auto-numbering that are exposed to the users and how dangerous they are.

>> This is a first take using natural compound PRIMARY KEYs… At this
point it becomes tempting to do: <<

No, you need to make the keys longer and longer as you go down the hierarchy o ri t will break.

CREATE TABLE Proposals
(proposal_nbr INTEGER NOT NULL PRIMARY KEY)

CREATE TABLE ProposalOptions
(PRIMARY KEY (Proposal_nbr, proposal_option_id)   FOREIGN KEY proposal_nbr REFERENCES Proposals(proposal_nbr))

CREATE TABLE Proposal_Option_Items
(PRIMARY KEY (proposal_nbr, proposal_option_id, proposal_option_item_id),
FOREIGN KEY proposal_nbr
  REFERENCES Proposals(proposal_nbr),
FOREIGN KEY (proposal_nbr, proposal_option_id)   REFERENCES ProposalOptions (proposal_nbr, proposal_option_id)

Etc.

Now you need to see which constraints are redundant and where you want to put DRI actions. Received on Fri Sep 05 2003 - 22:12:04 CEST