CODEWITHSUNDEEP
laravellaravel-4laravel-5
user3489502
user3489502

Reputation: 3611

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

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();

Upvotes: 22

Views: 14537

Answers (4)

Nikolay Antonov
Nikolay Antonov

Reputation: 160

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.

Upvotes: 2

rocambille
rocambille

Reputation: 15996

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...

Upvotes: 12

shempignon
shempignon

Reputation: 562

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.

Upvotes: 5

Kryten
Kryten

Reputation: 15780

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.

Upvotes: 16

Related Questions