MySQL returns "Cannot add or update a child row: a foreign key constraint fails"

Question

I saw the similar questions but none of them satisfied my situation.

I have 2 tables:

User(id, apartmentId, ....)
Apartment(id, ....)

When User is created, the apartmentId is null, when user joined an apartment, the apartmentId will be set.

The problem is, after user LEFT his or her apartment, I need to set apartmentId to null, but because of the foreign key constraint MySql doesn't allow me to do so, but as you can see this is a make-sense case.

Other than dropping foreign key constraint, is there any other approach?

Here is my code

    public bool QuitApartment(string userId)
    {
        var user = _userService.GetById(userId);
        if(user == null)
        {
            return false;
        }

        user.ApartmentId = null;

        _dataContext.Entry(user).State = EntityState.Modified;
        _dataContext.SaveChanges();
        return true;
    }

'because of the foreign key constraint' - seems unlikely but you need to provide the full table definitions and the processes you are carrying out to cause an issue (ie insert,update etc) — P.Salmon, Jun 10 '19 at 12:09
hi @P.Salmon I added my code. the full table definitions are not necessary. — Franva, Jun 10 '19 at 12:19
Always show the exact exception, message + type! If this really throws an FK violation exception the user can't have been inserted with `ApartmentId = null`. — Gert Arnold, Jun 10 '19 at 12:24
hi @GertArnold when I insert a new user, it is fine to have ApartmentId null, it only becomes a problem when I am trying to update the ApartmentId to null. — Franva, Jun 11 '19 at 13:28
How clearly should I state it? `ApartmentId` can or can't be null, independent of inserting or updating. What's the *exact* column definition? Also, you still didn't supply the exact exception message. — Gert Arnold, Jun 11 '19 at 14:37
thanks @GertArnold for explaining it. I have solved the problem with the solution I posted, so I do not have the exception screenshot any more. — Franva, Jun 12 '19 at 15:00

score 0 · Answer 1 · answered Jun 12 '19 at 14:59

I found that there is no way that you can update the apartmentId from a valid ID back to null (as the database rules prevent so) and at same time, maintain the foreign key constraint.

So my final solution is to drop the Foreign key constraint which solved my problem.

I hope to see some better solutions which should allow me to

Keep the foreign key constraint
Allow meaningful "null value update"

Cheers

MySQL returns "Cannot add or update a child row: a foreign key constraint fails"

1 Answers1