The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi...

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

Tanks..

DRauber

asked 19 Oct '12, 08:13

DRauber's gravatar image

DRauber
20671018
accept rate: 0%

edited 19 Oct '12, 08:13


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.

permanent link

answered 19 Oct '12, 08:47

Reimer%20Pods's gravatar image

Reimer Pods
4.1k324278
accept rate: 12%

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:)

(19 Oct '12, 09:26) Volker Barth

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

(19 Oct '12, 09:38) DRauber
Replies hidden

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.

(19 Oct '12, 10:18) Volker Barth

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 = ...)
(19 Oct '12, 10:21) Volker Barth

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

(19 Oct '12, 10:41) DRauber

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

(19 Oct '12, 10:43) DRauber
showing 2 of 6 show all flat view
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:

×22

question asked: 19 Oct '12, 08:13

question was seen: 2,724 times

last updated: 19 Oct '12, 10:43