StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL - many-to-many table primary key
text
Body
copied!<p>This question comes up after reading a comment in this question:</p> <p><a href="https://stackoverflow.com/questions/2190089/database-design/2190101">Database Design</a></p> <p>When you create a many-to-many table, should you create a composite primary key on the two foreign key columns, or create a auto-increment surrogate "ID" primary key, and just put indexes on your two FK columns (and maybe a unique constraint)? What are the implications on performance for inserting new records/re-indexing in each case?</p> <p>Basically, this:</p> <pre><code>PartDevice ---------- PartID (PK/FK) DeviceID (PK/FK) </code></pre> <p>vs. this:</p> <pre><code>PartDevice ---------- ID (PK/auto-increment) PartID (FK) DeviceID (FK) </code></pre> <p>The commenter says:</p> <blockquote> <p>making the two IDs the PK means the table is physically sorted on the disk in that order. So if we insert (Part1/Device1), (Part1/Device2), (Part2/Device3), then (Part 1/Device3) the database will have to break the table apart and insert the last one between entries 2 and 3. For many records, this becomes very problematic as it involves shuffling hundreds, thousands, or millions of records every time one is added. By contrast, an autoincrementing PK allows the new records to be tacked on to the end.</p> </blockquote> <p>The reason I'm asking is because I've always been inclined to do the composite primary key with no surrogate auto-increment column, but I'm not sure if the surrogate key is actually more performant.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload