MySQL "FOREIGN_KEY_CHECKS" SQLAnywhere 17 alternative

[Former Member]

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?

[VolkerBarth · Accepted solution]

Do you want to disable FK checks within one single transaction? For that, SQL Anywhere allows to choose whether FK relationships are

1. checked at the end of the according DML statement (by default) or
2. are delayed until commit time.

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...

[Former Member · Accepted solution]

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 SET OPTION WAIT_FOR_COMMIT = 'on/off'

[Breck_Carter · Accepted solution]

> 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.

[VolkerBarth · Accepted solution]

Yes, that particular statement was exactly the reason for me to question that feature. One for the legendary MySQL FAQ, I guess?

[Former Member · Accepted solution]

I don't insert rows in tables while foreign_key_checks=0. I only truncate tables with foreign_key_checks=0 and then immediately enable it back by foreign_key_checks=1 and only after enabling it I insert data in tables. Am I doing something wrong?

[VolkerBarth · Accepted solution]

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.

[johnsmirnios]

See the wait_for_commit option: http://dcx.sap.com/index.html#sqla170/en/html/8154916c6ce21014a231f4f6ff4d77aa.html*loio8154916c6ce2...