Laravel 5: SQLSTATE[23000]: Integrity constraint violation

Question

I have a form with the following fields: name, fullname, description, email, password, state, city (filled through Ajax when the state is chosen) and photo (a image upload). Here's my schema:

public function up()
{
    Schema::create('states', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('acronym');
        $table->string('name');
    });

    Schema::create('cities', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name');
        $table->integer('state_id')->unsigned();
        $table->foreign('state_id')->references('id')->on('states');
    });

    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name');
        $table->string('fullname');
        $table->string('photo');
        $table->text('description');
        $table->string('email');
        $table->string('password', 60);
        $table->integer('city_id')->unsigned();
        $table->foreign('city_id')->references('id')->on('cities');
        $table->rememberToken();
    });
}

My problem is that when I press the submit button, the following error message appears:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (yearbook.users, CONSTRAINT users_city_id_foreign FOREIGN KEY (city_id) REFERENCES cities (id)) (SQL: insert into users (name, description, email) values (Gabriel, Description, email@gmail.com))

So the problem is related to the city_id being a foreign key (the 'value' attribute from the option at the form is the id). Fro exemple:

<option value="281">Abaíra</option>

But when I try to insert through phpmyadmin, it inserts with no errors.

So what could be the problem?

Here's my save method at the controller:

public function saveRegister()
{
    $rules = array(
        'name' => 'required',
        'description' => 'required',
        'fullname' => 'required',
        'email' => 'required',
        'password' => 'required',
        'state' => 'required',
        'city' => 'required',
        'photo' => 'required',
        'photo' => 'image|max:3000000',
    );
    $val = \Validator::make(\Input::all(), $rules);

    if($validar->fails()){
        return redirect('register')->withErrors($val);
    } else {
        $user = new User(array(
            'name' => \Input::get('name'),
            'description' => \Input::get('description'),
            'fullname' => \Input::get('fullname'),
            'email' => \Input::get('email'),
            'city_id' => \Input::get('city'),
        ));
        $user->timestamps = false;
        $user->save();
        return redirect('register')->with('message', 'User saved!');

    }
}

Edit: fixed city_id at saveRegister() method.

Answer 1

instead of

$user = new User(array(
        'name' => \Input::get('name'),
        'description' => \Input::get('description'),
        'fullname' => \Input::get('fullname'),
        'email' => \Input::get('email'),
        'city' => \Input::get('city'),
    ));

do this

$user = new User();
$user->fill(array(
        'name' => \Input::get('name'),
        'description' => \Input::get('description'),
        'email' => \Input::get('email')
    ));
$user->fullname = \Input::get('fullname');
$user->city= \Input::get('city');

or change the $fillable property in the model

Answer 2

It may be because your cities.id field is not unsigned whereas your FK fields are unsigned. All columns used in relations must match exactly.

Try changing your code to this:

Schema::create('cities', function(Blueprint $table)
{
    $table->increments('id')->unsigned();