2

I am studying Computer Science, and recently in the exam, I had the following question:

“Write the SQL query creating this ER model.”

The model has three (3) tables: person, library, and the third table contains two foreign keys linked respectively on person's id and library's id.

In the middle table (the foreign keys one), it wasn't indicated if the foreign keys were making the primary so I asked him to make sure, and he told me this table had no primary key.

That left me confused, and refused to explain more, and just said “it works without a primary key, we saw this in class.”

Because the model was a capture from phpmyadmin's designer view and because he didn't really justified. I feel suspicious toward this and I think he tried it, and that MySQL let him do it but that is wrong. Can someone explain this better? I'm totally fine with being wrong, I just want to know.

To make it a bit clearer, he precised that there was no primary key at all and not just the two foreign key together like I thought.

Orsu
  • 405
  • 6
  • 19

1 Answers1

7

The "middle" table is usually referred to as a junction table. In this case, it represents relationships between people and libraries. When your teacher mentioned that this table has no primary key, it could be that the table has no formal primary key defined. Of course, internally MySQL needs to assign a unique ID to each record to internally keep track of the table. But there may not be an explicit primary key. However, most likely there is a composite primary key in this table composed of the combination of the people and libraries columns. And, also most likely, this pair of values is unique in each row, unless a given relationship could be repeated. If such repetitions exist, then the table is not normalized.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    Yeah it's what i thought, usually i take two foreign keys together to create a primary one, but in this case he *especially* said "no primary key at all" – Orsu Oct 26 '17 at 11:37
  • @Orsu He may have said one thing, but I'll bet he understands how the middle table is supposed to be functioning. – Tim Biegeleisen Oct 26 '17 at 11:38
  • 1
    Maybe he expected me to create one anyway, with "add constraint primary key blabla" but then i'd find very confusing that he said "No primary key" – Orsu Oct 26 '17 at 11:40
  • 2
    Taken what he said literally, it could only mean that there are duplicate relationships between people and their libraries. E.g. `Fred` has library `L1` appears twice in that junction table. But if so, then it is not normalized and it's bad design. If there _aren't_ duplicates in that table, then there _is_ a primary key, whether or not he thinks it, and whether or not you explicitly defined one. – Tim Biegeleisen Oct 26 '17 at 11:42
  • I agree. The two IDs in the bridge table would of course be non-nullable and you would want to avoid duplicates for which you'd have a unique constraint (unique index) on the pair. A primary key is nothing else than this: a unique constraint on non-nullable columns. (Only if you have more such constraints, e.g. an additional surrogate ID for each record, also non-nullable and unique, you'd usually make *one* of them the *primary* key explicitly, but this doesn't change anything.) – Thorsten Kettner Oct 26 '17 at 12:11