SQL Integrity constraint violation on route PATCH /users/{id}

[TeddyRoncin]

Description

By passing the following body to route PATCH /users/{id} :

{
  "addresses": [
    {
      "street": "string",
      "postalCode": "00 000",
      "city": "string",
      "country": "string"
    }
  ]
}

We get an error 500 (Internal Server Error).

If we pass anything else, as long as the body does not contain the entry addresses :

{
  "socialNetwork": {
    "facebook": "https://facebook.com/foobar",
    "twitter": "https://twitter.com/foobar",
    "instagram": "https://instagram.com/foobar",
    "linkedin": "https://linkedin.com/foobar",
    "pseudoDiscord": "foobar",
    "wantDiscordUTT": true
  },
  "RGPD": {
    "isKeepingAccount": true,
    "isDeletingEverything": true
  },
  "preference": {
    "birthdayDisplayOnlyAge": true,
    "language": "fr",
    "wantDaymail": true,
    "wantDayNotif": true
  },
  "infos": {
    "sex": "Masculin",
    "nickname": "string",
    "passions": "string",
    "website": "https://foobar.com"
  },
  "mailsPhones": {
    "mailPersonal": "user@example.com",
    "phoneNumber": "0123456789"
  }
}

The return code is 200.

Even if we pass an empty list to addresses:

{
  "addresses": []
}

The return code is 500

Investigation

First, the stack trace provided by the API in the response body can help us understand the problem :

In the console, we can find the SQL request executed. Let's take the easier example, with the empty addresses list. We get the following line : As we can see, the API is trying to set the field user_id (in the table user_address) to null.

By looking into the database structure, we can find that $addresses is the only field of the User entity which has a one to many relationship (meaning each User have multiple UserAddress, and each UserAddress have one User) :

The error is not due to the fact that the link is stored in the user_address table, because the link between user_mails_phones and the users is stored in the table user_mails_phones. Here is the schema representing the model the user_address model.