cascade constraints

[VolkerBarth]

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?

[VolkerBarth]

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.

[Former Member]

In PostgreSQL one need not remove constraint prior to dropping, even if data exists, for example:

ft_node=# create table t1 (x numeric,y varchar(10));
CREATE TABLE
ft_node=# alter table t1 add constraint pk_t1 primary key (x);
ALTER TABLE
ft_node=# create table t2 (x numeric, y varchar(10));
CREATE TABLE
ft_node=# alter table t2 add constraint fk_t2 foreign key (x) references t1 (x);
ALTER TABLE
ft_node=# insert into t1 values (1,'Yellow');
INSERT 0 1
ft_node=# insert into t2 values (1,'Mellow');
INSERT 0 1
ft_node=# drop table t1 cascade;
NOTICE:  drop cascades to constraint fk_t2 on table t2
DROP TABLE
ft_node=# \\d+ t1
Did not find any relation named "t1".

Same goes for Oracle:

SQL> create table t1 (x numeric,y varchar(10));

Table created.

SQL> alter table t1 add constraint pk_t1 primary key (x);

Table altered.

SQL> create table t2 (x numeric, y varchar(10));

Table created.

SQL> alter table t2 add constraint fk_t2 foreign key (x) references t1 (x);

Table altered.

SQL> insert into t1 values (1,'Yellow');

1 row created.

SQL> insert into t2 values (1,'Mellow');

1 row created.

SQL> drop table t1 cascade constraints;

Table dropped.

SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist


Its really useful when you have to generate continuous builds when unit testing. Looks like you can't do something similar in SQL Anywhere?

[Breck_Carter]

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

[VolkerBarth]

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.

[Breck_Carter]

> it's not the expected behaviour

Have you been spending a lot of time using other products? 🙂

[VolkerBarth]

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

[Breck_Carter]

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

[Breck_Carter · Accepted solution]

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 🙂