Any advantages of adding an id column to a pivot table in Laravel?

Question

Is there any advantages of having an id column in a pivot table (many to many relationship) in Laravel (i'm using version 5.1)?

With an id

        $table->increments('id');

        $table->integer('appointment_id')->unsigned();
        $table->foreign('appointment_id')->references('id')->on('appointments')->onDelete('cascade');

        $table->integer('client_id')->unsigned();
        $table->foreign('client_id')->references('id')->on('clients')->onDelete('cascade');

        $table->timestamps();

Without an id

        $table->integer('appointment_id')->unsigned();
        $table->foreign('appointment_id')->references('id')->on('appointments')->onDelete('cascade');

        $table->integer('client_id')->unsigned();
        $table->foreign('client_id')->references('id')->on('clients')->onDelete('cascade');

        $table->timestamps();

Does `$object->delete()` work if you don't have a primary key? (I think one is created automatically for `increments`, but my experience is mostly Laravel 4...) — Matt Gibson, Dec 14 '15 at 19:35
Yes, it works for me, it deletes everything, the parent row and relations in my pivots tables, but I use delete Cascade. — user3489502, Dec 14 '15 at 19:51
It would be the same as having an auto-incrementing `id` column on a standard pivot table: it doesn't hurt to have one, but they're ultimately redundant, as you're (most likely) never going to be querying a pivot table directly. Example `SELECT * FROM pivot_table WHERE id = 5;` Unless you know that you specifically want that row, there's really no point. — Tim Lewis, Dec 14 '15 at 20:40
Wouldn't it be useful for syncing in Laravel ? e.g `$user->roles()->sync([1, 2, 3]); ` http://laravel.com/docs/5.1/eloquent-relationships#inserting-many-to-many-relationships — user3489502, Dec 14 '15 at 20:56
Might make it easier to delete, but not by much, I would imagine. I noticed that with the model relations you define, I don't have much interaction with the pivot table itself... except for delete. — Cheesus Toast, Dec 29 '21 at 00:51

score 16 · Accepted Answer · answered Dec 14 '15 at 20:35

16

In general, the answer is no, provided that Laravel's Eloquent models are managing the relationship.

If, however, you need to access the tables from outside of Eloquent models (say, from another application or in the distant future when you rewrite your application to use the next big framework), an ID will come in handy.

answered Dec 14 '15 at 20:35

Kryten

15,230
6
45
68

Thanks, it makes sense. – user3489502 Dec 14 '15 at 20:56
2

old but, having a unique id in the pivot also lets you edit table entries directly from sql clients/sql workbench. – Miguel Felipe Guillen Calo May 20 '20 at 19:48

score 12 · Answer 2 · answered Jul 03 '18 at 15:50

12

Late answer, but you can remove the auto increment id, and use your foreign keys as a composite primary key for your pivot table:

$table->integer('appointment_id')->unsigned();
$table->foreign('appointment_id')->
    references('id')->on('appointments')->onDelete('cascade');

$table->integer('client_id')->unsigned();
$table->foreign('client_id')->
    references('id')->on('clients')->onDelete('cascade');

// add the following instruction

$table->primary(['appointment_id', 'client_id']);

Tested with Laravel 5.6, this works without breaking Eloquent's management while ensuring the uniqueness of the associations directly in your database structure.

More about creating indexes...

answered Jul 03 '18 at 15:50

rocambille

15,398
12
50
68

What? I always heard that composite keys are not, and will never be supported on Laravel – nowox Apr 16 '19 at 18:48
@nowox from the [doc](https://laravel.com/docs/5.6/migrations#creating-indexes) : "You may even pass an array of columns to an index method to create a compound (or composite) index". So I guess it is supported :) – rocambille Apr 17 '19 at 09:23
1

Then I guess you can create tables with composite index, but you [cannot use in Eloquent](https://stackoverflow.com/a/31415545/2612235) – nowox Apr 17 '19 at 11:36

score 5 · Answer 3 · answered Dec 15 '15 at 10:00

5

Be careful while adding an id as primary key on pivot tables as it will "break" any validation on the foreign key couple unicity, meaning you could have multiple records having the same foreign keys couple.

answered Dec 15 '15 at 10:00

shempignon

562
3
10

1

Hmm, you're right. I guess, the only work around that is adding a unique index on both of the foreign key couple `$table->unique(array('appointment_id', 'client_id'));` – user3489502 Dec 15 '15 at 12:30
2

I do not think this should be an issue since the `id` column will be `AUTO_INCREMENT` – Damilola Olowookere Apr 30 '18 at 07:48
Yes, if you have an auto increment id field, then you need to do a unique pair constraint. – Cheesus Toast Dec 29 '21 at 00:43

score 2 · Answer 4 · answered Nov 15 '18 at 16:45

If you are going to set up MySQL Group Replication - you have to have the Primary key on every table.

17.7.1 Group Replication Requirements Server instances that you want to use for Group Replication must satisfy the following requirements.

Infrastructure InnoDB Storage Engine. Data must be stored in the InnoDB transactional storage engine. Transactions are executed optimistically and then, at commit time, are checked for conflicts. If there are conflicts, in order to maintain consistency across the group, some transactions are rolled back. This means that a transactional storage engine is required. Moreover, InnoDB provides some additional functionality that enables better management and handling of conflicts when operating together with Group Replication.

Primary Keys. Every table that is to be replicated by the group must have a defined primary key, or primary key equivalent where the equivalent is a non-null unique key. Such keys are required as a unique identifier for every row within a table, enabling the system to determine which transactions conflict by identifying exactly which rows each transaction has modified.

IPv4 Network. The group communication engine used by MySQL Group Replication only supports IPv4. Therefore, Group Replication requires an IPv4 network infrastructure.

Network Performance. Group Replication is designed to be deployed in a cluster environment where server instances are very close to each other, and is impacted by both network latency as well as network bandwidth.

"...must have a defined primary key OR PRIMARY KEY EQUIVALENT where the equivalent is a non-null unique key" Adding the id on pivot tables is a big waste of time and space, not to mention the big issue with consistency, since you can have duplicated relationship between two entities. So, unless you have plans to make those pivot records actual entities, or are in fact Entities in your business, it is really an ugly design to have that extra id field — Luis Enrique Ramirez Noy, Apr 03 '19 at 22:23
When I read that it does not seem to indicate the need for an incremental id field. It just says: it needs a primary key. The 2 values in a pivot table should be the primary key as far as I know. What they are asking in original post (I think), is whether you should put an incremental id field, like what most other tables have. — Cheesus Toast, Dec 29 '21 at 00:55

Any advantages of adding an id column to a pivot table in Laravel?

4 Answers4