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, 12:27

bluefrog's gravatar image

bluefrog
10316
accept rate: 0%

1

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.

(24 Jul, 13:45) Volker Barth
Replies hidden

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?

(24 Jul, 15:46) bluefrog

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

(24 Jul, 17:10) Breck Carter

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.

(24 Jul, 17:52) Volker Barth

> it's not the expected behaviour

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

(25 Jul, 10:14) Breck Carter

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

(25 Jul, 17:37) Volker Barth

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

(26 Jul, 07:57) Breck Carter
showing 2 of 7 show all flat view

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

permanent link

answered 24 Jul, 17:07

Breck%20Carter's gravatar image

Breck Carter
26.6k433604879
accept rate: 21%

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:

×28
×6

question asked: 24 Jul, 12:27

question was seen: 89 times

last updated: 26 Jul, 07:59