CODEWITHSUNDEEP
sqlpostgresql
Lecko
Lecko

Reputation: 1405

Primary key in "many-to-many" table

I have a table in a SQL database that provides a "many-to-many" connection.

The table contains id's of both tables and some fields with additional information about the connection. 

CREATE TABLE SomeTable (
  f_id1 INTEGER NOT NULL,
  f_id2 INTEGER NOT NULL,
  additional_info text NOT NULL,
  ts timestamp NULL DEFAULT now()
);

The table is expected to contain 10 000 - 100 000 entries.

How is it better to design a primary key? Should I create an additional 'id' field, or to create a complex primary key from both id's?

DBMS is PostgreSQL

Upvotes: 2

Views: 2455

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

This is a "hard" question in the sense that there are pretty good arguments on both sides. I have a bias toward putting in auto-incremented ids in all tables that I use. Over time, I have found that this simply helps with the development process and I don't have to think about whether they are necessary.

A big reason for this is so foreign key references to the table can use only one column.

In a many-to-many junction table (aka "association table"), this probably isn't necessary:

  • It is unlikely that you will add a table with a foreign key relationship to a junction table.
  • You are going to want a unique index on the columns anyway.
  • They will probably be declared not null anyway.

Some databases actually store data based on the primary key. So, when you do an insert, then data must be moved on pages to accommodate the new values. Postgres is not one of those databases. It treats the primary key index just like any other index. In other words, you are not incurring "extra" work by declaring one more more columns as a primary key.

My conclusion is that having the composite primary key is fine, even though I would probably have an auto-incremented primary key with separate constraints. The composite primary key will occupy less space so probably be more efficient than an auto-incremented id. However, if there is any chance that this table would be used for a foreign key relationship, then add in another id field.

Upvotes: 7

Bla...
Bla...

Reputation: 7288

Yes that's actually what people commonly do, that key is called surrogate key.. I'm not exactly sure with PostgreSQL, but in MySQL by using surrogate key you can delete/edit the records from the user interface.. Besides, this allows the database to query the single key column faster than it could multiple columns.. Hope it helps..

Upvotes: 1

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

A surrogate key wont protect you from adding multiple instances of (f_id1, f_id2) so you should definitely have a unique constraint or primary key for that. What would the purpose of a surrogate key be in your scenario?

Upvotes: 4

Related Questions