how to delete an account after a post request

Question

On my method accountRepo.deleteAll() return error:

2020-06-04 15:37:05.069 ERROR 78168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ОШИБКА: UPDATE or DELETE in table "account" violates foreign key constraint "fk8k31xl4ld2m810mxfkqp2xg8g" from table "tokens"
Details: on a key (account_id)=(2) there are still links in the table "tokens".
2020-06-04 15:37:05.071  INFO 78168 --- [           main] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

What could be the problem? This happens when creating a post-request with the registration of a user who will fall into accountRepo

My test and post-request:

@Test
@Throws(Exception::class)
fun shouldRegistrationExpected201() {

    val headers = HttpHeaders()
    headers.contentType = MediaType.APPLICATION_JSON
    val request = HttpEntity<String>("{\"username\": \"holker228\", \"password\": \"123456QQwerty&&\",\"email\":\"test2@mail.ru\",\"is_collective\": \"false\" }", headers)
    val responseEntity = restTemplate.postForEntity("http://localhost:$port/api/user/registration", request, String::class.java)

    assertEquals(responseEntity.statusCode, HttpStatus.CREATED)
}

Answer 1

The issue seems to be in constrains configured in your database. BTW, which one do you use? In your particular case the constraint disallows DELETE (UPDATE?) operation as the row you're trying to delete seems to have dependent rows in another table.

E.g, imagine you have a table "users" and "comments":

users:

-------------
| ID | Name |
-------------
| 1  | Max  |
-------------
| 2  | Vova |
-------------
| 3  | Vlad |
-------------

comments:

---------------------------------
| ID | User ID | Text           |
---------------------------------
| 1  | 1 | Max was here!        |
---------------------------------
| 2  | 1 | Vova + Vlad = ❤️      |
---------------------------------
| 3  | 2 | Don't listen to Max! |
---------------------------------

Here, if the constrains are configured, you cannot delete Max (1) or Vova (2) from "users" as they have comments. Imagine, you're rendering an HTML page and you need to render a comment's author's name? What would you do, if the author is deleted? (well, you can use "Anonymous", but let's not think about it). You cannot either change Max's or Vova's IDs for the same reason. So that's what constraints are for: they prevent such unconsistencies.

Ways to deal with this situation:

1. Update the related entities in the same transaction.
2. Use CASCADE for DELETEs. Take a look at this answer, for example
3. Freeze the IDs and do not change them.