We have a table that has the primary key as a auto increment called unique_id, the table has a account number that is set as a foreign key to another table.
For some reason the program was not checking when account numbers are entered to see if that account number was already used, which will be fixed.
The issue is a user entered a new account with a account number already being used.
I now need to figure out how to safely remove the account they entered, when I try to run a sql delete statement to remove it I get the error.
Is there a save way to delete the record so it is no longer in the table, I am using the primary key unique_id for the record in the where statement to delete it.
When the query runs I get this: Cannot delete or update a parent row: a foreign key constraint fails
Discussion posts and replies are publicly visible
Here you would have to delete the record in the child table first, then you can delete the parent.
Are you able to update it instead to a new/correct ID? I avoid deleting data at all costs, but I could see this as one use case.
I would definitely suggest adding validation if the users are manually entering account numbers, you can take what then enter and do a simple a!queryEntity() to see if the totalCount is 1 (already added), then prevent submission.