when a foreign key references a primary key in the same table in mysql

Question

In laravel referral system i want two foreign keys(userId,parentId) if user registers using referral code

I defined two foreign keys (userId,parentId) in the network table when a user registers with a referral code. It's id, and its parent ID get saved in network table with userId and parentId, respectively, but I'm finding it difficult, so I recently saw a new method to define a foreign key references on primary key in the same table something like:

$table->foreign('parent_id')
        ->references('id')
        ->on('users')
        ->onUpdate('cascade')
        ->onDelete('set null');

But not sure how it works, so any guidance.

Answer 1

for those users who have no referral, they can't have parent_id. but you have not defined that parent_id can be null. for the current code, it need to have a parent_id.

change parent_id non-null to nullable. here is the updated code

$table->foreign('parent_id')
        ->nullable() // add nullable so that parent_id can be null
        ->references('id')
        ->on('users')
        ->onUpdate('cascade')
        ->onDelete('set null');