Re: Referential constraint in On-Line 7
Hi,
I think I can both understand the confusion and explain it.
I just did some testing with the attached script on a 7.20.UC1 OnLine
database (on a Sun Sparc 10 running Solaris 2.4). Using DB-Access in the
menu mode, it certainly appeared that the bug cited was found. I nearly
panicked! But before doing that, I checked what happened using my SQLCMD
program, and it seemed to work correctly -- the subsidiary row in table B
was deleted too. So I ran DB-Access in 'dbaccess dbase script' mode, and
it showed that the behaviour was correct -- no rows existed after the
delete.
I went back to DB-Access in menu mode, and the line that appeared to show
that the cascaded delete did not work is revealed as an interloper; it is
actually the result of the first select from table B, not the second.
Screen dump (multiple blank lines compressed to a single line):
===========================================================================
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- apt@anubis_41 ---------- Press CTRL-W for Help --------
(constant) a0 a1
(constant) b0 b1
B values: 1 1 All done
Table dropped.
===========================================================================
I think that something like this may be the cause of your confusion.
If not, then you need to show how you demonstrate the problem, please.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
CREATE TABLE a
(
a0 SERIAL NOT NULL PRIMARY KEY CONSTRAINT pk_a,
a1 CHAR(10) NOT NULL
);
INSERT INTO a VALUES(0, "Hi there");
CREATE TABLE b
(
b0 INTEGER REFERENCES a(a0) ON DELETE CASCADE CONSTRAINT fk_b,
b1 CHAR(10) NOT NULL
);
INSERT INTO b VALUES(1, "All done");SELECT 'A VALUES: 1', * FROM a;
SELECT 'B VALUES: 1', * FROM b;
DELETE FROM a WHERE 1 = 1;SELECT 'A VALUES: 2', * FROM a;
SELECT 'B VALUES: 2', * FROM b;
DROP TABLE b;
DROP TABLE a;
>Date: Tue, 11 Jun 1996 21:56:19 -0500 (CDT)
>From: Cheryl Kendricks <cherylk@prod1.jcdc.doleta.gov>
>To: wyang <wyang@aerotek.com>
>X-Informix-List-Id: <list.10253>
>
>What exact version did you find this on?
>
>On Tue, 11 Jun 1996, wyang wrote:
>
>} It seems that Informix On-line 7 supports cascading deletes when a
>} referential integrity is violoated. When I create a foreign key
>} constrait from dbaccess, it prompts me for "Add enable cascading deletes
>} Yes or No". I chose Yes because I thought this would allow me to delete
>} the referenced column without getting any error. But actually it does
>} not work that way.
>}
>} I search all informix manuals, but didn't find anything about cascading
>} deletes of foreigh key.
>}
>} Any comments are appreciated.
>} Weimin