Lets say I have table A with two junction tables B and C, how would I go about creating primary keys for table A? I have two of these types of table in a diagram I drew, the circle keys are foreign keys btw.

Image with junction tables


2 Answers

No'am Newman On Best Solutions

Your games table needs only one primary key: this identifies each specific game. In the junction tables, the primary keys are composed of the game primary key and the directors (or types) primary key.

GMc On

Taken from the reference in the tutorial MySQL Primary Key:

   role_name VARCHAR(50),
   PRIMARY KEY(role_id)

It is difficult to provide information about your specific question because there is too little details in it.

From your comment "if a table has two junction tables attached to it, would it need to have two primary keys?". No.

A primary key is actually a logical concept (a design mechanism) used to define a logical model. A primary key is a set of attributes (columns) that together uniquely identify each end every Tuple (row) in a relation (table). One of the rules of a primary key is that there is only one per relation.

The logical model is used, as mentioned, as the design to create the physical model, relations become tables, attributes become columns, Primary keys may become unique indexes. Foreign Keys may become indexes in the related table and so on.

Many RDBMS's allow the specification of a PRIMARY KEY in a physical table definition. Most also allow definition of FOREIGN KEYs on a physical table also. What they do with them may vary from one implementation to another. Many use the definition of a PRIMARY KEY to define a UNIQUE INDEX of some sort to enforce the "must uniquely identify" each and every record in the table.

So, No, your games_directors table does not need, nor can it have, two primary keys. if you did choose to specify a PRIMARY KEY, you would need to specify all the columns that uniquely identify records in the games_directors table - most likely PRIMARY KEY (game_id, director_id).

Similarly, the PRIMARY KEY for the games table would likely be PRIMARY KEY (game_id), for the directors would likely be PRIMARY KEY (director_id) and for game types it would likely be PRIMARY KEY (game_type_id).

You might use a foreign key from your games_directors table to ensure that when records are added to it that the corresponding director exists in the games table and the directors table. In this case, your games_directors table will have two foreign key relationships (one to games and another to directors). But only one PRIMARY KEY.

So you might end up with something like this:

create table games (
     game_id integer,
     PRIMARY KEY (game_id)

create table directors (
    director_id integer,
    PRIMARY KEY (director_id)

CREATE TABLE games_directors (
    game_id     INTEGER NOT NULL,
    director_id INTEGER NOT NULL,
    commission_paid DECIMAL(10,2),

  PRIMARY KEY (game_id, director_id),
  FOREIGN KEY (game_id) REFERENCES games(game_id),
  FOREIGN KEY (director_id) REFERENCES directors(director_id)

NB: I didn't tested the above using PostgreSql. The syntax should work for most RDBMS's, but some may require tweaking slightly.

Indexes can be used to speed up access to individual records within table. For example, you might want to create an index on director name or director id (depending upon how you most frequenytly access that table). If you mostly access the director table with an equality condition like this : where director_name = 'fred' then an index on director_name might make sense.

Indexes become more useful as the number of records in the tables grows.

I hope this answers your question. :-)