First time posting here although I am fairly sure this specific situation hasn't already been addressed. The closest one I have found has been where someone was using their primary keys in a JOIN statement which was causing their issue, however this is not the case here.
Luckily I can keep this fairly simple as the database is still under development. If it matters I am doing all the work in MySQL Workbench.
I have a table
projects, which among other information has
project_id as the primary key, into which I have inserted a load of test projects and now have tried deleting one of them:
DELETE FROM `database_name`.`projects` WHERE (`project_id` = '38');
Only to get the below:
Operation failed: There was an error while applying the SQL script to the database. ERROR 1175: 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
But of course,
project_id is quite literally the primary key.
What I have attempted so far:
- removing the parentheses from the WHERE clause before attempting to execute it
projects.before the column name in the WHERE clause
- using a different column in the WHERE clause (
project_namewhich is a unique key)
- Every combination of the above (so 1 and 2, 1 and 3, 2 and 3, and all 3)
What on earth could possibly be causing this? My MariaDB version is 10.4.25.
In the comments it has been mentioned that it could in fact be my foreign keys causing this issue, as deleting data in this table then attempts to delete data in other tables not by their primary key. So now to figure out a way around that!
Final edit, solution found!
Well, more of a workaround than a solution in my mind, but I'll leave that up to you.
So whilst it was indeed SQL safe mode preventing me from deleting data from other tables by columns that are not keys in that table (it was in the
BEFORE DELETE trigger rather than anything to do with foreign keys), SQL safe mode itself is not set at the server level - it can be set at the server level, but generally isn't - it was set in my Workbench.
So the solution/hack/workaround within that trigger is below:
SET SQL_SAFE_UPDATES = 0; DELETE FROM projects_change_log WHERE project_id = OLD.project_id; SET SQL_SAFE_UPDATES = 1;
Of course, always make sure to reactivate safe mode when you're done needing it to not be activated, as it's still a good safety mechanism!