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?

asked 17 Jul, 03:13

ilyas_khametov's gravatar image

ilyas_khametov
31116
accept rate: 100%


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

permanent link

answered 17 Jul, 05:41

Volker%20Barth's gravatar image

Volker Barth
32.2k328474688
accept rate: 32%

edited 17 Jul, 05:54

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'

(17 Jul, 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, 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, 04:15) Volker Barth

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?

(20 Jul, 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, 06:17) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×29
×16
×9

question asked: 17 Jul, 03:13

question was seen: 115 times

last updated: 20 Jul, 06:18