Sql-server – How to get dependencies order

There's currently no way to modify which unique index a foreign key constraint is "attached to" because this is supposed to be an internal implementation detail, the same way that a unique constraint uses a unique index behind the scenes to enforce the constraint.

In fact, as you saw, if you have multiple candidate indexes, the one the foreign key attaches to seems non-deterministic (it might go by index id, index width, or something else; I haven't played with it in-depth), and it cannot be specified in the DDL statement either (memory says there's a Connect item requesting that feature, though).

So in this case, at least for now, you'll need to drop both the foreign key and the existing unique constraint before re-establishing the relationship, to ensure your change script works correctly in all scenarios.

I've tried to do something similar some years ago and the SQL required defeated me as well!

The approach I took was to use gawk to extract all the statements I wanted from the unload file and then run that file. The whole approach could potentially be run in a batch file using the SQLA command line utility dbunload.exe, then gawk, then isql to run the extracted commands. How easy this approach is depends on how happy you are with complex regular expressions etc.

Another approach would be to use the sa_migrate_create_fks() system procedure - it is well documented in the help, but there would be several stages involved.

Alternatively, repost the question on the dedicated SQLA forum http://sqlanywhere-forum.sap.com which is monitored by SQLA developers who may be able to help with a SQL construct.

Good luck