Primary Key and ER models

Question

Say i have an ER model

 _____________
|     E2      |
|_____________|
  |    |    |
  |    |    |
 A21* A22  A23*

where the A21 and A23 are primary keys.

Would this mean the primary key for E2 will be

PRIMARY KEY(A21, A23)?

If so, then what is the difference of the diagram below.

 _____________
|  E2         |
|_____________|
  |    |    |
  |    |    |
  []---|----[]---[]
  |    |    |
  |    |    |
 A21* A22  A23*

where the [] are representation of a combination of primary keys. is this the same as the previous diagram and the primary key being PRIMARY KEY (A21, A23)?

Then, based on the first diagram, should there be SQL code, can the statement REFERENCES E2(A21) and REFERENCES E2(A23) exist? Or do we have to always reference all the primary keys in a table?

I hope my question is clear enough.

Answer 1

assuming i understand your question correctly the sql for the table would be

CREATE TABLE AKS.E2 ( a21 NUMBER, a22 NUMBER, a23 NUMBER );

ALTER TABLE AKS.E2 ADD ( CONSTRAINT E2_PK PRIMARY KEY (a21, a23));

you can have foreign key contraints like REFERENCES E2(A21) or REFERENCES E2(A23) even though they reference partial keys of a compund primary key.

Answer 2

I don't recognise the diagram notation you are using (designating keys with asterisks). Typically in an ER diagram key attributes are underlined (Chen notation) or are separated from other attributes by a line (IDEF1X).

One of the limitations with most ER notations is that they don't make it easy to show multiple candidate keys. Often only one key (the "primary key") per entity is shown. So at a guess I would say that if you have multiple key attributes shown on your diagram then they are probably all part of the same compound key.

A foreign key is only supposed to reference an entire candidate key, not parts of a key.

Answer 3

The image is originally from this question/answer, I will use it to illustrate few basic points.

1. Foreign keys reference full primary keys.

2. ER is quite good at representing composite keys.

3. There are quite a few nice ER tools available (some open source) -- use one.

Take a look at the Answer table

create table Answer (
      SurveyID        integer
    , QuestionID      integer
    , OfferedAnswerID integer
    , PersonID        integer
    , OtherText       varchar (2000)
);

alter table Answer
    add constraint pk_answer
                   primary key (SurveyID, QuestionID, OfferedAnswerID, PersonID)

  , add constraint fk2_answer
                   foreign key (SurveyID, QuestionID, OfferedAnswerID)
                   references Survey_Question_Answer (SurveyID, QuestionID, OfferedAnswerID)

  , add constraint fk1_answer
                   foreign key (PersonID) references Person (PersonID)
;