Should a database table that contains two columns that are foreign keys have a third column which is the primary key?

Answer 1

The answer is usually "yes". The kind of table you describe is an association table, which stores associations. Because these records are interesting in their own right, and because you will probably want to look them up later, they should have a meaningful identity.

For instance, perhaps you have a players table and a matchups table for your tennis league. matchups may contain nothing more than the foreign keys of the two players that played against each other; it's an association between two players.

But later you may want to record other information specific to that association: the time the match occurred, the score of the game, et cetera. And, of course, as soon as you want to have more than one matchup between the same two players, you'll need to differentiate between each matchup. Thus you'll want to give each matchup its own identity in the form of a primary key.

---

Update:

========================
| galleries_images     |
|----------------------|
| FK | gallery_id      |
| FK | image_id        |  <----- Should I add a PK to this table?
========================

In your specific example, it's probably useful to have a primary key here. As soon as you need to record any metadata about the association, you'll want to have that primary key. Also, if the same image can be added to the same gallery more than once, a primary key will be necessary to differentiate between the two records.

Answer 2

You say that the foreign keys are primary keys in their own tables. That means that they are unique in those tables. It doesn't mean that they are unique in this table, however.

I have generally found that it is best to create a primary key on a database table. Sooner or later, you are going to discover that you need it, so why not include the new primary key from the start?

Answer 3

All tables should have a primary key.

It is not necessary to create a new surrogate column to act as primary key though. Taking John's example it would be perfectly acceptable to have a composite primary key with the 2 primary key fields from other tables and a date field.

From a pragmatic point of view though sometimes creating a new surrogate column can be easier to work with than a composite one though if the PK is itself referenced in yet another table or for binding to various controls that don't handle composite primary key's well.

Edit

Following the update to your question I would just make the primary key composite on gallery_id, image_id. I don't see any benefit of adding a new column.

Answer 4

In theory, if the combination of the two foreign keys (FKs) is unique in the table, or if the combination of the two FKs plus some other column is unique, then the table has a compound primary key and there is no strict need to introduce another key as a surrogate primary key. However, it is not unusual to find that people do add an extra key. In part, it depends on what else the data in the table with the compound primary key will used for. If it describes something that will itself have rows from other tables associated with it, then it may make sense to introduce a simple PK.

Some software seems to require simple PKs, even though the Relational Data Model does not.

Answer 5

If one specific image can only be associated with one single gallery, then the combinations in your galleries-images table are unique, and you may use that pair of fields as the PK.
If the galleries-images combinations can be duplicated OR
if your schema includes more tables that are goind to be childs of that galleries-images,
THEN I would suggest you do include an extra field that will be the PK.

Answer 6

This answer has to do with his locking question: I know, should be new topic but whatever. Please don't downvote simply for that.

For example you could create a table Order_image_lock (gallery ID (primary key), start_time).

Create 3 methods/sprocs: GetLock, CheckLock, DropLock.

When you want to reorder a portfolio, you Call GetLock which inserts (gallary_id, sysdate).

If it works, you can proceed. If it fails on the PK, someone else is reordering, raise exception.

When you're ready to Reorder, call CheckLock to see if your lock is still there (you'll see why) if you have it, update the reordered values, if not go to GetLock.

When you're done, DropLock Deletes the record.

A server process can sweep the table for locks more than x minutes old. For disconnects or people who leave the screen up and head to lunch.

Add a user_id column to that table as well, so you can report back who has what locks another user may want.

This will scale far better than actually locking the rows. some dbms's have a finite amount of locks, which forces them to perform 'lock escalation' where multiple row locks are converted to a page lock until there are too many page locks and are converted to a table lock... you need to check how your RDBMs works with large lock volumes... if you plan to scale.