Hi, The problem is that the reference from baz to bar is not 'on delete cascade':
> create table baz (id bigint identity, foo_id bigint references foo on > delete cascade, bar_id bigint references bar /* _not_ cascade */); I don't know why PostgreSQL allows to delete the row, actually it looks like a bug in PostgreSQL. Apache Derby doesn't allow to create this table with the following exception: Foreign Key 'SQL110619115850770' is invalid because 'the delete rule of foreign key must be CASCADE. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '. 42915/30000 MySQL will simply not cascade the delete (I guess MySQL doesn't support cascading deletes, I didn't investigate). If you use cascade deletes in the second constraint it will work as you expect in all databases (except MySQL): create table baz (id int primary key, foo_id int references foo on delete cascade, bar_id int references bar on delete cascade); Regards, Thomas On Mon, Jun 13, 2011 at 10:47 PM, Eric Jain <[email protected]> wrote: > create table foo (id bigint identity); > create table bar (id bigint identity, foo_id bigint references foo on > delete cascade); > create table baz (id bigint identity, foo_id bigint references foo on > delete cascade, bar_id bigint references bar); > > insert into foo (id) values (1); > insert into bar (id, foo_id) values (1, 1); > insert into baz (id, foo_id, bar_id) values (1, 1, 1); > > delete from foo where id = 1; > > The delete is cascaded to bar, but that record can't be deleted > because it's referenced from baz. Compare this to PostgreSQL, which > realizes that the delete also cascades to the record in baz, and will > go ahead and delete the three records from foo, bar and baz. > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
