laravel set up a foreign key

Question

I am very new in laravel.I am doing a project where user first of all sign up and his information will store in a User table.then he or she sign in to the website and the user will provide another form for registration as a Blood donner. This imformation will store another table namely blooddonners table.so i want to make a foreign key in blooddonner table.The foreingn key will be the id from the user table.Thats why i have created a migration for blooddonner table like that,

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBlooddonnerTable extends Migration {

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('blooddonners', function(Blueprint $table)
    {
        $table->increments('id');
        $table->unsignedInteger('donner_id');
        $table->foreign('donner_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
        $table->string('donner_name');
        $table->string('email')->unique();
        $table->string('blood_group');
        $table->string('phone_number')->unique();
        $table->string('location');
        $table->date('date_of_birth');
        $table->date('last_date_of_donation');
        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('blooddonners');
}

}

but after submitting the the form,i am facing the following error,

Illuminate \ Database \ QueryException
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row:    a foreign key constraint fails (`needa`.`blooddonners`, CONSTRAINT    `blooddonners_donner_id_foreign` FOREIGN KEY (`donner_id`) REFERENCES `users` (`id`) ON    DELETE CASCADE ON UPDATE CASCADE) (SQL: insert into `blooddonners` (`date_of_birth`,    `blood_group`, `location`, `email`, `phone_number`, `last_date_of_donation`, `updated_at`,    `created_at`) values (2014-08-06, A-, SHERPUR, raihn_cse@yahoo.com, 01796580404, 2014-08-  20, 2014-08-10 19:42:38, 2014-08-10 19:42:38))

though i am very new in Laravel,so i have question that,the way i have set foreign in my migration file ,is it the rigth way to set foreign key ?, cause the error is related to that foreign key.

Answer 1

There's nothing wrong with the way you're setting up foreign keys. Proof of that is that MySQL is actually stopping you from inserting an entry which violates the foreign key you've told it to enforce. The problem is that you're trying to add a blood donor entry without specifying which user it is related to. You have two options, then:

1. In the query you are using to add the new blood donor, specify the related user's id;
2. Even though donor_id is the foreign key, it can be nullable if that's what you want, though you need to tell Laravel / MySQL that.

Here's how to define a nullable column:

$table->unsignedInteger('donor_id')->nullable();

I've taken the liberty of correcting your spelling: blood donner > blood donor. Proper spelling helps code readability, too ;)

---

Edit

If you want a suggestion on how you can improve your database design in order to make good use of foreign keys, here's how I'd approach it:

First thing: I'd only have a secondary blood_donors table if not every user was a blood donor. If 100% of your users are blood donors, don't over-complicate things: use only one table and add more columns. Otherwise, read on.

My users table would probably have all columns that relate to general personal information:

• name
• email
• phone number (unique on users)
• location
• date of birth

After that I can just "link" the users table and the blood_donors table via foreign keys so I don't have to repeat myself. All I need to know is which user corresponds to which blood donor, and all I need to accomplish that is an id. That way, when I do a query on the database, whether it's on the blood_donors or users table originally, I can easily join both tables together and have all the data at once, without having it repeated in two different places.

So, going back to your migration, this would now suffice:

Schema::create('blooddonners', function(Blueprint $table)
{
    $table->increments('id');
    $table->unsignedInteger('user_id');
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
    $table->string('blood_group');
    $table->date('last_date_of_donation');
    $table->timestamps();
});

i.e. you don't need a "donor name" in your blood_donors table, because you already have it recorded on your users table, and they are the same person after all. You should have columns which are pertinent only in the context of donating blood.

You can then access all this data with a query like this (assuming you already have your models set up):

User::join('blood_donors', 'users.id', '=', 'blood_donors.user_id')->get();

And you'd have an object with both tables' columns at once. Just be mindful that columns with the exact same name will override each other in your final object, so if you want to reliably access both ids within that object, you'd better specify aliases on the select portion of the query. You can even change the column names if you have to. In Laravel you can do it like this:

User::select('users.*', 'users.name as donor_name', 'blood_donors.id as donor_id')
    ->join('blood_donors', 'users.id', '=', 'blood_donors.user_id')
    ->get();

Take a look at the Eloquent documentation on relationships to see how you can take your database designs further and still maintain clean, readable code.