mysql - 违反完整性约束 : 1452 Cannot add or update a child row

Question

我遇到了这个错误:

SQLSTATE[23000]:违反完整性约束:1452 无法添加或更新子行:外键约束失败(pumma2。users，CONSTRAINT users_roles_id_foreign FOREIGN KEY (roles_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE) (SQL: 插入 用户(用户名，电子邮件，姓名，密码，封面 , roles_id, updated_at, created_at) 值 (qweqwe, haikalhikmi@gmail.com, qweqweqew, $2y$10$YX2OPPTGEDcquYkgk.ln6eEuwtFqrtmRbIpvgEZqJJHR9gk8mfdfm, wallpaper- 27058.jpg, 1, 2016-12-11 06:09:41, 2016-12-11 06:09:41))

这是我的注册 Controller

$user=$request->file('cover');
        $destination ='img/user';
        $filename=$user->getClientOriginalName();
        storage::put('img/user/'.$filename,file_get_contents($request->file('cover')->getRealPath()));
 
        $user = new User();
                
        $user->username = $request->username;
        $user->email = $request->email;
        $user->name = $request->name;
        $user->password = bcrypt($request->password);
        $user->cover = $filename;
        $user->roles_id = DB::table('roles')->select('id')->where('rolename','user')->first()->id;       
                 
        $user->save();

当我尝试 dd($user) 时:

有效:

attributes: array:6 [▼
    "username" => "qweqwe"
    "email" => "haikalhikmi@gmail.com"
    "name" => "qweqweqew"
    "password" => "$2y$10$Ci5usZFP6ANCyBfQhN3Gzexh98Wb8R9n4AmVEvq8x/4bakfGF8l/y"
    "cover" => "wallpaper-27058.jpg"
    "roles_id" => 1
  ]

以防万一你想看我的用户表

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('roles_id')->nullable();
            $table->string('username')->unique();
            $table->string('email')->unique();
            $table->string('name')->unique();
            $table->string('password');
            $table->string('cover');
            $table->rememberToken();
            $table->timestamps();
        });

        Schema::create('roles', function (Blueprint $table){
            $table->increments('id');
            $table->string('rolename');
        });

        schema::table('users', function (Blueprint $table){            
            $table->foreign('roles_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
            
        });

    }

Answer 1

从您上面的代码中，用户与角色有属于关系。所以用户外键的迁移应该是

schema::table('users', function (Blueprint $table){            
        $table->foreign('roles_id')->references('id')->on('roles')->onDelete('cascade')->onUpdate('cascade');

});  

roles_id 列应该引用 roles 表中的 id 列，而不是 users 表。

更改外键应该可以帮助您摆脱约束冲突。

更新(在下面的评论中对 OP 的查询进行了更多解释)

在问题中的RegisterController代码中

$user->roles_id = DB::table('roles')->select('id')->where('rolename','user')->first()->id;  

抛出异常，因为 DB::table('roles')->select('id') - 这部分将返回一个整数，从这里链接到 ->where( 'rolename','user')->first()->id 将类似于尝试在普通整数上查找属性，这将导致异常。

所以查询可以定义为

$user->roles_id = DB::table('roles')->where('rolename', 'user')->first()->id;

/* 
    Or - to get just the id instead of the entire record 
 */ 

$user->roles_id = DB::table('roles')->where('rolename', 'user')->value('id'); 

/* 
    Or - if there is a Role model defined corresponding to the roles table 
 */

$user->roles_id = \Role::where('rolename', 'user')->first()->id;