Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
employees table
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateEmployeesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->increments('emp_id');
$table->string('emp_fname');
$table->string('emp_lname');
$table->date('emp_bdate');
$table->string('gender');
$table->integer('emp_mobile')->unique();
$table->integer('emp_alt_mobile')->unique();
$table->string('emp_email')->unique();
$table->string('emp_address');
$table->string('emp_photo');
$table->string('emp_username');
$table->string('password');
$table->string('emp_state');
$table->string('emp_city');
$table->string('emp_country');
$table->integer('emp_pincode');
$table->string('emp_id_proof');
$table->integer('login_first');
$table->timestamps();
});
Schema::table('employees', function (Blueprint $table) {
$table->integer('department_id')->unsigned()->after('emp_id');
$table->foreign('department_id')->references('department_id')->on('departments')->onDelete('cascade')->change();
$table->integer('designation_id')->unsigned()->after('department_id');
$table->foreign('designation_id')->references('designation_id')->on('designations')->onDelete('cascade')->change();
$table->integer('report_to_id')->unsigned()->after('designation_id');
$table->foreign('report_to_id')->references('emp_id')->on('employees')->onDelete('cascade')->change();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('employees');
Schema::table('employees', function (Blueprint $table) {
$table->dropForeign('employees_department_id_foreign');
$table->dropForeign('employees_designation_id_foreign');
$table->dropForeign('employees_report_to_id_foreign');
});
}
}
Hey @kanchan186
I believe you already have some data in designations table in the database. If so, this is the reason for the error. You need to empty that table first then hit the migration. If you cannot delete the table data, you need to pass it like this:
$table->integer('designation_id')->unsigned()->after('department_id')->nullable();
@nomguy still getting same error
Okay @kanchan186 . No problem. There's a simple hack which could let you keep going. Just replace the code of up() function with this:
Schema::disableForeignKeyConstraints();
Schema::create('employees', function (Blueprint $table) {
$table->increments('emp_id');
$table->string('emp_fname');
$table->string('emp_lname');
$table->date('emp_bdate');
$table->string('gender');
$table->integer('emp_mobile')->unique();
$table->integer('emp_alt_mobile')->unique();
$table->string('emp_email')->unique();
$table->string('emp_address');
$table->string('emp_photo');
$table->string('emp_username');
$table->string('password');
$table->string('emp_state');
$table->string('emp_city');
$table->string('emp_country');
$table->integer('emp_pincode');
$table->string('emp_id_proof');
$table->integer('login_first');
$table->timestamps();
});
Schema::table('employees', function (Blueprint $table) {
$table->integer('department_id')->unsigned()->after('emp_id');
$table->foreign('department_id')->references('department_id')->on('departments')->onDelete('cascade')->change();
$table->integer('designation_id')->unsigned()->after('department_id');
$table->foreign('designation_id')->references('designation_id')->on('designations')->onDelete('cascade')->change();
$table->integer('report_to_id')->unsigned()->after('designation_id');
$table->foreign('report_to_id')->references('emp_id')->on('employees')->onDelete('cascade')->change();
});
Schema::enableForeignKeyConstraints();
And let me know if it helps.
using above code getting error
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_partsanalysis`.`employees`, CONSTRAINT `employees_designation_id_foreign` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE CASCADE) (SQL: insert into `employees` (`emp_fname`, `emp_lname`, `emp_email`, `emp_bdate`, `emp_mobile`, `emp_alt_mobile`, `emp_country`, `emp_state`, `emp_city`, `emp_address`, `emp_pincode`, `gender`, `department_id`, `designation_id`, `emp_photo`, `emp_id_proof`, `emp_username`, `password`, `login_first`, `updated_at`, `created_at`) values (star, Garage, [email protected], 1989-03-03, 989775654, 977565423, 105, 12, 19, pimple gurav, 411061, Female, 1, 0, download.png, 3687122335, 12ews, 123W1, 0, 2019-11-21 20:19:07, 2019-11-21 20:19:07))
Please check your designation table. There is no field named designation_id in your designation table.
OR
Make sure you have designation_id in the fillable property of your Employee model.
@ajithlal sir
designation_id is present in designations table and also in fillable of employee model
@kanchan186 Can you show me designation table migration?
designations table
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateDesignationsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('designations', function (Blueprint $table) {
$table->increments('designation_id');
$table->string('designation_name');
$table->timestamps();
});
Schema::table('designations', function (Blueprint $table) {
$table->integer('department_id')->unsigned()->after('designation_id');
$table->foreign('department_id')->references('department_id')->on('departments')->onDelete('cascade')->change();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('designations');
Schema::table('designations', function (Blueprint $table) {
$table->dropForeign('designation_department_id_foreign');
});
}
}
Please share the Employee model code. @kanchan186
Do you have a Designation record with id 0; because your query is attempting to insert an Employee record and associate it with a designation_id = 0
I would suggest you check that the code which is creating the record is getting the correct data, especially with regard to designation_id.
employee model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Employee extends Model
{
protected $fillable = [
'emp_fname','emp_lname', 'emp_bdate', 'gender','emp_mobile','emp_alt_mobile','emp_email','emp_country','emp_state','emp_city','emp_pincode','emp_address','emp_photo','emp_username','password','emp_cat_id','department_id','designation_id','emp_id_proof','login_first'];
protected $primaryKey = 'emp_id';
}
designation table
You don't have a designation_id with value 0. Please check designation dropdown in your employee create page.
@ajithlal sorry sir but i can't understand what are u saying?
@kanchan186 I already mentioned above that the query is attempting to write a designation_id 0 for the INSERT - you do not have a designation record with this id, so the foreign key constraint is failing.
You need to check the logic that is creating the Employee record - see where the designation_id is coming from and address the issue there. If you are processing a form, then a simple validation of the data would prevent the query exception being thrown.
@tykus so where i need to change code and how to do that?
this is my code and logic to insert employee data
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Department;
use App\Designation;
use App\Employee;
use App\City;
use App\Country;
use App\State;
use File;
class EmployeeController extends Controller
{
public function __construct()
{
$this->middleware('auth');
}
public function Add()
{
$department=Department::get();
$designation=Designation::get();
$employee=Employee::get();
$country=Country::where('country_name','India')->first();
$state=State::where('country_id',$country->country_id)->get();
$city = City::all();
$country=Country::get();
return view('backend.employee.addEmp',compact('department','employee','designation','state','city','country'));
}
public function cityAjax($state)
{
$city=City::where("state_id",$state)
->pluck('cities.city_name','cities.city_id')->all();
return json_encode($city);
}
public function store(Request $request)
{
//dd(request()->all());
$this->validate($request,[
'emp_fname'=>'unique:employees|required',
]);
if($request->hasFile('emp_photo')){
$file = $request->file('emp_photo');
$destinationPath = public_path().'/emp_photos/';
$filename = $file->getClientOriginalName();
if(!File::exists($destinationPath))
{
File::makeDirectory($destinationPath, $mode = 0777, true);
$file->move($destinationPath, $filename);
}
else
{
$file->move($destinationPath, $filename);
}
// echo $filename;exit;
}//end of if
$emp_id=employee::create([
//'emp_id' => request('emp_id'),
'emp_photo' =>$filename,
'emp_fname' => request('emp_fname'),
'emp_lname' => request('emp_lname'),
'emp_email' => request('emp_email'),
'emp_bdate' => request('emp_bdate'),
'emp_mobile' => request('emp_mobile'),
'emp_alt_mobile' => request('emp_alt_mobile'),
'emp_country' => request('emp_country'),
'emp_state' => request('emp_state'),
'emp_city' => request('emp_city'),
'emp_address' => request('emp_address'),
'emp_pincode' => request('emp_pincode'),
'gender' => request('gender'),
'department_id' => request('department_id'),
'designation_id' => request('designation_id'),
'emp_id_proof' => request('emp_id_proof'),
'emp_username' => request('emp_username'),
'password' => request('password'),
'login_first'=>request('login_first'),
'report_to_id' => request('report_to_id'),
'login_first' => 0,
]);
return redirect('employee/view');
}
public function show()
{
$employee=employee::join('departments','employees.department_id','=','departments.department_id')
->join('designations','employees.designation_id','=','designations.designation_id')
->get();
$department=Department::get();
$designation=designation::get();
//return $names;
return view('backend.employee.viewEmp',compact( 'department','employee'));
}
public function designationAjax($department)
{
$designation = Designation::where("department_id",$department)
->pluck('designations.designation_name')->all();
return json_encode($designation);
}
public function edit( $emp_id)
{
$employee=employee::findOrFail($emp_id);
$department=Department::get();
$designation=designation::get();
$country=Country::where('country_name','India')->first();
$state=State::where('country_id',$country->country_id)->get();
$city=City::where('state_id',$employee->emp_state)->get();
$country=country::get();
//dd($department);
return view('backend.employee.editEmp',compact('employee','department','designation','country','state','city'));
}
public function update(Request $req,Employee $employee)
{
//dd(request()->all());
if($req->hasFile('emp_photo')){
$files=public_path().'/emp_photos/'.$req->input('emp_photo1');
File::delete($files);
//dd($files);
$file = $req->file('emp_photo');
$destinationPath = public_path().'/emp_photos/';
$filename = $file->getClientOriginalName();
$file->move($destinationPath, $filename);
// echo $filename;
}
else{
$filename=$req->input('emp_photo1');
}
$employee->update([
'emp_fname' => request('emp_fname'),
'emp_lname' => request('emp_lname'),
'emp_email' => request('emp_email'),
'emp_bdate' => request('emp_bdate'),
'emp_mobile' => request('emp_mobile'),
'emp_alt_mobile' => request('emp_alt_mobile'),
'emp_country' => request('emp_country'),
'emp_state' => request('emp_state'),
'emp_city' => request('emp_city'),
'emp_address' => request('emp_address'),
'emp_pincode' => request('emp_pincode'),
'gender' => request('gender'),
'department_id' => request('department_id'),
'designation_id' => request('designation_id'),
'emp_photo' =>$filename,
]);
return redirect('employee/view');
}
public function destroy(Employee $employee)
{
$files=public_path().'/emp_photos/'.$employee->emp_photo;
File::delete($files);
$employee->delete();
return redirect('employee/view');
}
}
If you add this rule, you will fail with a validation exception before attempting to insert the record into the database
$this->validate($request,[
'emp_fname'=>'unique:employees|required',
'designation_id' => 'exists:designations,id' // checks if the is a record matching the provided value
]);
You need to check why you are getting 0 from the request.
using this code it gets error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select count(*) as aggregate from `designations` where `id` = 0)
As @tykus said You need to check why you are getting value 0 for the field designation_id from the request.
@kanchan186 can you show your employee create form code?
using this code it gets error. SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select count(*) as aggregate from
designationswhereid= 0)
Update the validate function given by @tykus like below.
$this->validate($request,[
'emp_fname'=>'unique:employees|required',
'designation_id' => 'exists:designations,designation_id' // checks if the is a record matching the provided value
]);
form code
<form class="m-t-40" method="post" action="{{url('/')}}/employee" enctype="multipart/form-data">
{{csrf_field()}}
<div class="form-group">
<h5>Employee First Name <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_fname" id ="emp_fname" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Employee Last Name <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_lname" id ="emp_lname" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Employee Email <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_email" id ="emp_email" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Employee DOB <span class="text-danger">*</span></h5>
<div class="controls">
<input type="date" name="emp_bdate" id ="emp_bdate" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Employee mobile <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_mobile" id ="emp_mobile" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Employee alternate mobile <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_alt_mobile" id ="emp_alt_mobile" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Select Country <span class="text-danger">*</span></h5>
<div class="controls">
<select class="form-control" name="emp_country" id="emp_country" onchange="getState(this.value)" required>
<option>--Select country--</option>
@foreach($country as $br)
<option value="{{$br->country_id}}">{{$br->country_name}}</option>
@endforeach
</select>
</div>
</div>
<div class="form-group">
<h5>Select State <span class="text-danger">*</span></h5>
<div class="controls">
<select class="form-control" name="emp_state" id="emp_state" onchange="getCity(this.value)" required>
<option>--Select State--</option>
@foreach($state as $st)
<option value="{{$st->state_id}}">{{$st->state_name}}</option>
@endforeach
</select>
</div>
</div>
<div class="form-group">
<h5>Select City <span class="text-danger">*</span></h5>
<div class="controls">
<select class="form-control" name="emp_city" id="emp_city" required>
<option>--Select City--</option>
@foreach($city as $ct)
<option value="{{$ct->city_id}}">{{$ct->city_name}}</option>
@endforeach
</select>
</div>
</div>
<div class="form-group">
<h5>Employee Address <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_address" id ="emp_address" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Pincode <span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_pincode" id ="emp_pincode" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Gender <span class="text-danger">*</span></h5>
<div class="controls">
<div class="controls">
<input class="form-check-input" type="radio" name="gender" id="inlineRadio1" value="Male">
<label class="form-check-label" for="inlineRadio1" selected>Male</label>
<input class="form-check-input" type="radio" name="gender" id="inlineRadio2" value="Female">
<label class="form-check-label" for="inlineRadio2">Female</label>
</div>
</div>
</div>
<div class="form-group">
<h5>Select Department <span class="text-danger">*</span></h5>
<div class="controls">
<select class="form-control" name="department_id" onchange="getDesignation(this.value)" required>
<option>--Select Department--</option>
@foreach($department as $br)
<option value="{{$br->department_id}}">{{$br->department_name}}</option>
@endforeach
</select>
</div>
</div>
<div class="form-group">
<h5>Select Designation <span class="text-danger">*</span></h5>
<div class="controls">
<select class="form-control " name="designation_id" required>
<option>--Select Designation--</option>
</select>
</div>
</div>
<div class="form-group">
<h5>Employee photo<span class="text-danger">*</span></h5>
<div class="controls">
<input type="file" name="emp_photo" id ="emp_photo" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Employee ID Proof<span class="text-danger">*</span></h5>
<div class="controls">
<input type="text" name="emp_id_proof" id ="emp_id_proof" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
<div class="form-group">
<h5>Username <span class="text-danger">*</span></h5>
<div class="controls">
<div class="controls">
<input type="emp_username" name="emp_username" id ="emp_username" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
</div>
<div class="form-group">
<h5>Password <span class="text-danger">*</span></h5>
<div class="controls">
<div class="controls">
<input type="password" name="password" id ="password" class="form-control" required data-validation-required-message="This field is required"> </div>
</div>
</div>
<div class="text-xs-right">
<button type="submit" class="btn btn-info">Submit</button>
<button type="reset" class="btn btn-inverse">Cancel</button>
</div>
</form>
script
<script type="text/javascript">
function getDesignation(department)
{
if(department) {
$.ajax({
url: '{{url('/')}}/designation/ajax/'+department,
type: "GET",
dataType: "json",
success:function(data) {
$('select[name="designation_id"]').empty();
$('select[name="designation_id"]').prepend('<option value="">--Select designation--</option>');
$.each(data, function(key, value) {
$('select[name="designation_id"]').append('<option value="'+ key +'">'+ value +'</option>');
});
}
});
} else{
$('select[name="designation_id"]').empty();
}
}
</script>
What is the primary key on the designations table (designation_id?); modify the exists rule accordingly:
'designation_id' => 'exists:designations,designation_id'
For thedesignation_id,it appears you are using an array key (starting from 0) rather than the actual id from the table
<select class="form-control " name="designation_id" required>
<option>--Select Designation--</option>
</select>
Check with console.log if you have the IDs from the database in the AJAX response:
function getDesignation(department) {
if(department) {
$.ajax({
url: '{{url('/')}}/designation/ajax/'+department,
type: "GET",
dataType: "json",
success:function(data) {
console.log(data);
$('select[name="designation_id"]').empty();
$('select[name="designation_id"]').prepend('<option value="">--Select designation--</option>');
$.each(data, function(key, value) {
$('select[name="designation_id"]').append('<option value="'+ key +'">'+ value +'</option>');
});
}
});
} else{
$('select[name="designation_id"]').empty();
}
}
If not, you need to ensure that the IDs are returned, and these values should be used as the value attribute for each option in the select,
'designation_id' => 'exists:designations,designation_id'
it gives ->The selected designation id is invalid.
@tykus in his employee table migration he creating foreign key relationship with designation_id on designations table. That's why I suggested him to update his validation.
@kanchan186 you have to update your designationAjax() function. As you are fetching only the designation name the keys will be 0, 1, 2, etc. pluck designation_id also.
public function designationAjax($department)
{
$designation = Designation::where("department_id",$department)
->pluck('designation_name','designation_id')->all();
return json_encode($designation);
}
it gives ->The selected designation id is invalid.
Exactly! That is the validation failure. If you select the first option for Designation, it will submit 0 (arrays are 0-indexed) - this is not a valid option, so validation fails.
I told you earlier that the value attribute should be the actual IDs of designation records, you need to examine what is the response to the AJAX request, and either (i) use the IDs that are returned to populate the select option value attributes, or (ii) if not present, you need to modify the AJAX endpoint to ensure that the IDs are returned, then back to (i) above
using above code, error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select count(*) as aggregate from `designations` where `id` = 1)
Oh man... this is hard work... You were already told to modify the exists validation rule according to the actual primary key column of the designations table, i.e. designation_id:
'designation_id' => 'exists:designations,designation_id'
@tykus if i selected for designation_id= 1 it works properly but if i selected for designation_id=2 it shows ->The selected designation id is invalid.
Please or to participate in this conversation.