Change all foreign keys delete to Cascade?

[Former Member]

Hi...

How i can change all foreign keys in ASA 9 database to cascade?

Tanks..

DRauber

[reimer_pods]

AFAIK you will have to drop each foreign key and recreated it with the new constraints.

ALTER TABLE "sirius"."TestTable" DROP FOREIGN KEY "FKTestTable";
ALTER TABLE "sirius"."TestTable" ADD CONSTRAINT "FKTestTable" NOT NULL FOREIGN KEY ( "RT_ID" ASC ) REFERENCES "dba"."RefTable ( "RT_ID" ) ON UPDATE CASCADE ON DELETE CASCADE;

You might be able to write a generic script the uses informations about the defines foreign keys, generate the ALTER statements and execute them (EXECUTE IMMEDIATE). While surely it's not a trivial task, that should be possible (YMMV).

Alternatively you could unload the structure to a reload.sql file and modify it accordingly for that task.

[VolkerBarth]

FWIW, writing a generic script is certainly doable, however, I've often found the SQL Anywhere system tables/views quite difficult to use for such cases. (I.e. IMHO it's not that easy to find out which columns of which tables to look for to get the required information, as long as constraints are included. - It's rather easy for table/column names or procedures and the like.)

And it has not become easier with the big system catalo change in v10...

That might be a situation where a few more samples could be helpful:)

[Former Member]

mmmm its hard work!

in another database server (postgresql) i can execute: delete cascade TestTable where condition;

Has ASA 9 any option to do this?

Tanks..

[VolkerBarth]

AFAIK, the options to declare FK relationships and RI actions like RESTRICT or CASCADE are rather similar for PostgreSQL and SQL Anywhere (and conform to the SQL standards).

Do you say you can call "delete cascade TestTable ..." in PostgreSQL, and that will delete rows from the TestTable and from all according child tables that are declared with ON DELETE RESTRICT (which would otherwise prevented because of the RESTRICT action)? - I'm not aware of such a statement in PostgreSQL.

[VolkerBarth]

Just in case you just would need this once (to "clean-up" dependent data), you could of course DELETE the child table rows beforehand - that might be easier, such as

delete from FkTestTable where RT_ID in
   (select RT_ID from RefTable where condition = ...);
....
delete from RefTable where condition = ...)

[Former Member]

Sorry, I confused things, you can not really do too well in postgres

[Former Member]

yes, I always do so, but I have many cascade foreign keys... Tanks a lot!