|
I want to truncate a table, which has foreign key constraints. In MySQL it is possible to disable foreign key checking during truncation by executing the following query: SET FOREIGN_KEY_CHECKS = 0 And then enabling it again by executing the next query: SET FOREIGN_KEY_CHECKS = 1 What is SQLAnywhere 17 way to disable/enable foreign key checks? |
|
Do you want to disable FK checks within one single transaction? For that, SQL Anywhere allows to choose whether FK relationships are
You can choose that both for each particular FOREIGN KEY clause via omitting or adding the CHECK ON COMMIT clause, or can choose that globally or on a per-connection base by setting the wait_for_commit option to Off vs. On. If you want to disable FK checks over transaction boundaries, you will have to drop the FK, "clean up your data", and re-add it lateron. There is no command to disable DRI constraints. You would need triggers to toggle between abled and disabled FK relationships. Here's a similar FAQ how to temporarily disable triggers - you would then need to put the FK checks within the trigger logic and then use a variable or the like to switch between doing and ignoring the checks. Just to add: I'm no MySQL expert at all, but I guess SQL Anywhere has no counterpart for the MySQL "foreign_key_checks" system variable, and for a good reason: The possibility to completely ignoring declared FK relationships seems to be a very questionable "feature" IMHO... It is necessary for the development process: just clear all the tables in database and fill it with the necessary data again. By the way, thanks for the detailed answer. I ended up using
(17 Jul '18, 11:57)
ilyas_khametov
1
> a very questionable "feature" No kidding! Here's what the MySQL docs say... Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency.
(18 Jul '18, 17:23)
Breck Carter
Replies hidden
Yes, that particular statement was exactly the reason for me to question that feature. One for the legendary MySQL FAQ, I guess?
(19 Jul '18, 04:15)
Volker Barth
I don't insert rows in tables while
(20 Jul '18, 00:32)
ilyas_khametov
Well, we can't tell what you do and if this leads to orphaned rows in child tables. The point is when you use wait_for_commit='On' to delay the foreign key checking or when you drop FKs and later re-add them, SQL Anywhere always makes sure that FK relations are correct, i.e. there cannot be orphaned rows in child tables at commit time when a FK relation is declared. - Say, if you drop the FK, delete a row from the parent table which is used as a FK in the child table or would insert a row in the child table with a FK not existing in the parent table (*), and then would try to re-add the FK declaration, that would fail with SQLCODE -194. In my understanding of the MySQL documentation, the mentioned MySQL variable when set back to 1 does not check existing data, so orphaned rows are possible. (*) - Note that at that step, there is no FK error as the FK declaration has been dropped, so it currently does not apply.
(20 Jul '18, 06:17)
Volker Barth
|
|
See the wait_for_commit option: http://dcx.sap.com/index.html#sqla170/en/html/8154916c6ce21014a231f4f6ff4d77aa.html*loio8154916c6ce21014a231f4f6ff4d77aa |
