[RandomlyKnighted]

I'm a little confused by what you mean by "You probably want to index up the BookCategories bridging table"

Can a table have more than one primary key? If so, then I'll make bookID and categoryID a primary key.

As far as the unique constraints, I'll have to refer to my old books to refresh my memory on those before I do anything on them.

[e_i_pi]

A table can only have one Primary Key, but that Primary Key can be comprised of multiple columns. When it is comprised of multiple columns, it is called a Composite Primary Key. Bridging (or Junction) Tables can only have Composite Primary Keys, not ordinary Primary Keys, since the table describes a many-to-many relationship, and therefore no single column can act as a Primary Key.

[Atli]

e_i_pi, on 24 July 2012 - 08:48 PM, said:

Bridging (or Junction) Tables can only have Composite Primary Keys, not ordinary Primary Keys, since the table describes a many-to-many relationship, and therefore no single column can act as a Primary Key.

Technically that's not correct though. All that's required of a bridge table is that it has a unique composite key on the two foreign keys, but there is nothing that says it has to be the primary key. When the rows of the bridge table need to be referenced elsewhere, it's simpler to have a single integer act as the primary key instead of the composite key.

[RandomlyKnighted]

You both are making me realize just how much I don't remember from when I originally learned databases.

[RandomlyKnighted]

Is this what you are referring to with the composite primary keys?

create unique index bookcategories_unique on bookcategories (bookID, categoryID);

Primary key (bookID, categoryID);

Unfortunately neither of the books I own have much information on composite primary keys and how to use them.

[JackOfAllTrades]

Yes, that is a composite primary key.

[RandomlyKnighted]

Awesome, thanks JackOfAllTrades!