Re: on delete cascade doesn't work...

On Fri, 16 Oct 1998, aurelio wrote: > I am working with Dynamic Server 7.3. When I use 'on delete cascade' the > cascade deleting don't take place. It migth... Thank you for including a simple reproduction of your problem; it makes life so much easier! I've compressed your code a little to save vertical space, but not altered its meaning. > CREATE TABLE E1 (p1 char(18) NOT NULL); > ALTER TABLE E1 ADD CONSTRAINT PRIMARY KEY (p1); > CREATE TABLE E12 ( > p1 char(18) NOT NULL, > p2 char(18) NOT NULL > ); > ALTER TABLE E12 ADD CONSTRAINT PRIMARY KEY (p1, p2); > CREATE TABLE E2 (p2 char(18) NOT NULL); > ALTER TABLE E2 ADD CONSTRAINT PRIMARY KEY (p2); > ALTER TABLE E12 ADD CONSTRAINT FOREIGN KEY (p2) > REFERENCES E2 ON DELETE CASCADE; > ALTER TABLE E12 ADD CONSTRAINT FOREIGN KEY (p1) > REFERENCES E1 ON DELETE CASCADE;> > insert into e1 values('a'); > insert into e2 values('b'); > insert into e12 values('a','b');> > delete from e1 where p1='a';> > [Informix][Dynamic Server][test] SQL Error (-692) : Key value for constraint > (admin.u103_11) is still being referenced. You don't indicate which platform you are running on. You also do not indicate whether you are working with a logged database. However, I think you must be working with an unlogged database -- I got the error when I used an unlogged database but not with either a logged database or a MODE ANSI database. I was testing with OnLine 7.30.UC1 on Sun Sparc 20 running Solaris 2.6. The program I used to connect to the database was compiled with 7.24.UC1 ESQL/C on the same machine. I'm not sure whether it is a bug or not; we'd need to read all the very fine print on constraint handling. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn