Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is it not good to have a primary key on a join table?

I was watching a screencast where the author said it is not good to have a primary key on a join table but didn't explain why.

The join table in the example had two columns defined in a Rails migration and the author added an index to each of the columns but no primary key.

Why is it not good to have a primary key in this example?

create_table :categories_posts, :id => false do |t|   t.column :category_id, :integer, :null => false   t.column :post_id, :integer, :null => false end add_index :categories_posts, :category_id add_index :categories_posts, :post_id 

EDIT: As I mentioned to Cletus, I can understand the potential usefulness of an auto number field as a primary key even for a join table. However in the example I listed above, the author explicitly avoids creating an auto number field with the syntax ":id => false" in the "create table" statement. Normally Rails would automatically add an auto-number id field to a table created in a migration like this and this would become the primary key. But for this join table, the author specifically prevented it. I wasn't sure why he decided to follow this approach.

like image 976
pez_dispenser Avatar asked May 19 '09 01:05

pez_dispenser


People also ask

Should a join table have a primary key?

A table must have exactly one primary key to qualify as relational, but that key can be composed of multiple columns. A foreign key, by contrast, is one or more fields or columns that corresponds to the primary key of another table. Foreign keys are what make it possible to join tables to each other.

Can table join without primary key?

Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION. The latter is technically not a join but can be handy for merging tables in SQL. In this article, I'll guide you through the different solutions with examples.

Can we join two tables with primary foreign key relation?

A Join clause is used for combining two or more tables in the SQL Server database based on their relative column or relationship with the primary and the foreign key.

What does primary key does not accept?

The primary key does not accept the any duplicate and NULL values. The primary key value in a table changes very rarely so it is chosen with care where the changes can occur in a seldom manner. A primary key of one table can be referenced by foreign key of another table.


1 Answers

Some notes:

  1. The combination of category_id and post_id is unique in of itself, so an additional ID column is redundant and wasteful
  2. The phrase "not good to have a primary key" is incorrect in the screencast. You still have a Primary Key -- it is just made up of the two columns (e.g. CREATE TABLE foo( cid, pid, PRIMARY KEY( cid, pid ) ). For people who are used to tacking on ID values everywhere this may seem odd but in relational theory it is quite correct and natural; the screencast author would better have said it is "not good to have an implicit integer attribute called 'ID' as the primary key".
  3. It is redundant to have the extra column because you will place a unique index on the combination of category_id and post_id anyway to ensure no duplicate rows are inserted
  4. Finally, although common nomenclature is to call it a "composite key" this is also redundant. The term "key" in relational theory is actually the set of zero or more attributes that uniquely identify the row, so it is fine to say that the primary key is category_id, post_id
  5. Place the MOST SELECTIVE column FIRST in the primary key declaration. A discussion of the construction of b(+/*) trees is out of the scope of this answer ( for some lower-level discussion see: http://www.akadia.com/services/ora_index_selectivity.html ) but in your case, you'd probably want it on post_id, category_id since post_id will show up less often in the table and thus make the index more useful. Of course, since the table is so small and the index will be, essentially, the data rows, this is not very important. It would be in broader cases where the table is wider.
like image 103
Matt Rogish Avatar answered Oct 12 '22 23:10

Matt Rogish