In PostgreSQL and Oracle one can specify "cascade" "cascade constraints respectively with the drop table to ensure foreign key constraints are no longer applied. This also allows for "dropping" a table in any order, which is useful during testing. I can't find a similar "cascade" in the drop table documentation. How can one achieve something similar? asked 24 Jul '17, 12:27 bluefrog |
The SQL Anywhere Help for DROP TABLE says "All indexes and keys for the table are dropped as well." That includes foreign keys; here's a demo: CREATE TABLE parent ( pkey INTEGER NOT NULL PRIMARY KEY ); INSERT parent VALUES ( 1 ); COMMIT; CREATE TABLE child ( child_pkey INTEGER NOT NULL PRIMARY KEY, parent_pkey INTEGER NOT NULL, CONSTRAINT fkey_parent FOREIGN KEY ( parent_pkey ) REFERENCES parent ( pkey ) ); INSERT child VALUES ( 111, 1 ); COMMIT; SELECT * FROM parent ORDER BY pkey; SELECT * FROM child ORDER BY child_pkey; pkey ----------- 1 child_pkey parent_pkey ----------- ----------- 111 1 Here's what child looks like before parent is dropped... -- dba.child (table_id 3005) in ddd17 - Jul 24 2017 4:56:38PM - Print - Foxhound © 2016 RisingRoad CREATE TABLE dba.child ( -- empty child_pkey /* PK */ INTEGER NOT NULL, parent_pkey /* FK */ INTEGER NOT NULL, CONSTRAINT ASA82 PRIMARY KEY ( child_pkey ) ); -- Parents of dba.child -- dba.parent -- Children -- none -- ALTER TABLE dba.child ADD CONSTRAINT fkey_parent NOT NULL FOREIGN KEY ( parent_pkey ) REFERENCES dba.parent ( pkey ) ON UPDATE RESTRICT ON DELETE RESTRICT; This code shows you can still insert a row in child after dropping parent... DROP TABLE parent; INSERT child VALUES ( 222, 2 ); COMMIT; SELECT * FROM child ORDER BY child_pkey; child_pkey parent_pkey ----------- ----------- 111 1 222 2 Here's what child looks like after parent was dropped... -- dba.child (table_id 3005) in ddd17 - Jul 24 2017 4:57:41PM - Print - Foxhound © 2016 RisingRoad CREATE TABLE dba.child ( -- 2 rows, 12k total = 4k table + 0 ext + 8k index child_pkey /* PK */ INTEGER NOT NULL, parent_pkey INTEGER NOT NULL, CONSTRAINT ASA82 PRIMARY KEY ( -- 8k child_pkey ) ); -- Parents of dba.child -- none -- -- Children -- none -- In other words, SQL Anywhere does things the way they should be done :) answered 24 Jul '17, 17:07 Breck Carter |
Do you mean cascading DELETE (which is supported by SQL Anywhere) or really dropping a table that has child records. In the latter case, you must alter the child table to drop the constraint before the parent table can be dropped.
In PostgreSQL one need not remove constraint prior to dropping, even if data exists, for example:
Same goes for Oracle:
Its really useful when you have to generate continuous builds when unit testing. Looks like you can't do something similar in SQL Anywhere?
> you must alter the child table to drop the constraint before the parent table can be dropped.
That would violate the Watcom Rule :)
Even as far back as V5 the Help says "Also, all indexes and keys for the table are dropped by the DROP TABLE statement."
Oops, did not know that, and in my book, it's not the expected behaviour - I would at least expect a warning about lost FKs...
But your sample proves me wrong.
> it's not the expected behaviour
Have you been spending a lot of time using other products? :)
Not really compared to SA.
I drop tables rarely, but when I do, I drop them in the opposite order I created them, so I rarely ran into that problem:)
> I drop them in the opposite order I created them, so I rarely ran into that problem
Just so other people are clear: in SQL Anywhere there is no "problem" if you drop tables in the same order you created them. As the original question stated, that makes it easy the write schema scripts that can be run multiple times to drop and recreate everything.
In fact, it is something I often do when creating reproducibles for this forum... I rarely get everything right the first time (or the second... or the fifth... :) so I have to keep re-running the scripts.
In other words, "drop them in the opposite order" is unnecessary effort.