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.
If I go to the cloud database and run the delete and unclick the enable foreign keys it deletes it, tested this in our DEV environment. Is there a way in the .sql file to tell it to ignore the foreign key so the .sql file can be imported to run the delete? For production to run this they expect to do the import of the .sql file.
I'm not positive regarding the cloud database, it is possible in MSSQL but this type of solution is something I would avoid.
Can your admins not access the Appian system itself? DB only?