Francisunoxx Francisunoxx - 4 months ago 20
PHP Question

How to create two Foreign Key on pivot table linked to one Primary Key

I'm trying to implement where I need to insert or save the current user that logged in. Inserting the recipient into user_id column works well but I need to manipulate who send the data I need to get the user id. I have two tables users and documents with a pivot table document_user.

users table

Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('email');
$table->string('username');
$table->string('password');
$table->string('remember_token');
});


documents table

Schema::create('documents', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->text('content');
$table->integer('category_id')->unsigned();
$table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
$table->timestamps();
});


document_user - pivot table

Schema::create('document_user',function (Blueprint $table)
{
$table->increments('id');
$table->integer('user_id')->unsigned();
$table->integer('document_id')->unsigned();

$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->foreign('document_id')->references('id')->on('documents')->onDelete('cascade');
$table->dateTime('dateReceived')->default(DB::raw('CURRENT_TIMESTAMP'));
});


DB Design:

DB


Note! I only insert few column in my users table migration just to save a line of text.


Model

User

public function documents()
{
return $this->belongsToMany('App\Models\Document', 'document_user', 'user_id', 'document_id');
}


Document

public function recipients()
{
return $this->belongsToMany('App\Models\User', 'document_user', 'document_id', 'user_id');
}


Inserting records based on the user's choice to pivot table works well. But when I try to rollback my migration and alter my pivot table to this.

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


I get a error it says:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`webdev`.`document_user`, CONSTRAINT `document_user_sender_id_foreign` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE) (SQL: insert into `document_user` (`document_id`, `user_id`) values (34, 10))


How can I achieve inserting the current user in my pivot table? So I can track who sends and receive the data. Any help would appreciated! Cheers!

Answer

Don't use a pivot table! Your better off adding a user_id column to your documents table. Make sure you have the correct eloquent relationships as well, that can cause errors.

Hope this helps!

Edit:

You wrote thaf you get the error as soon as you add this to your table:

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

It could be because your creating two rows called ┬┤sender_id┬┤. I hope that works!

Edit 2:

I looked around a bit online, and found this post on laracasts: http://laravel.io/forum/09-18-2014-foreign-key-not-saving-in-migration. Also, this error is normally thrown when your trying to get a null value from another table and put it into a row that isn't nullable.