Обсуждение: CASCADE PB

Hello,
I use postgreSQL 7.2.3 and I have trouble on deleting.
I would like to automaticly delete line in the 'coop' table when I dlete the
line in the 'fact' table but it is not ok.
Where is my mistake ?
This is my database:

create table coop (
id_coop serial not null,
nom_coop varchar(50) not null,
primary key(id_coop)
);

create table fact (
id_fact serial not null,
id_coop integer references coop(id_coop) on delete cascade,
date_fact timestamp not null,
montant_fact decimal(6,3),
primary key(id_fact)
);

Thank's for your support.

On Wed, 27 Nov 2002, fred wrote:

> Hello,
> I use postgreSQL 7.2.3 and I have trouble on deleting.
> I would like to automaticly delete line in the 'coop' table when I dlete the
> line in the 'fact' table but it is not ok.
> Where is my mistake ?

On delete cascade goes the other direction.  What you've written says that
for any coop row that is deleted associated fact rows should be removed
not the other way around.  AFAICT your schema allows multiple fact rows
to reference a single coop row which means deleting the coop row will fail
unless this is the only fact row.

> This is my database:
>
> create table coop (
> id_coop serial not null,
> nom_coop varchar(50) not null,
> primary key(id_coop)
> );
>
> create table fact (
> id_fact serial not null,
> id_coop integer references coop(id_coop) on delete cascade,
> date_fact timestamp not null,
> montant_fact decimal(6,3),
> primary key(id_fact)
> );
>
> Thank's for your support.

Ok,
Thank you for your quick answer,
I'm affraid I made a mistake on my mail.
I really wanted to automaticly delete line in the 'fact' table when I delete
a line in the 'fact' table.
But when I suppress a line in the 'coop' table I still have the related line
in the 'fact' table.
Do you think there is a mistake in my sql syntaxe ?

create table coop (
id_coop serial not null,
nom_coop varchar(50) not null,
primary key(id_coop)
);

create table fact (
id_fact serial not null,
id_coop integer references coop(id_coop) on delete cascade,
date_fact timestamp not null,
montant_fact decimal(6,3),
primary key(id_fact)
);


Thank's for your support.




On Wednesday 27 November 2002 17:36, Stephan Szabo wrote:
> On Wed, 27 Nov 2002, fred wrote:
> > Hello,
> > I use postgreSQL 7.2.3 and I have trouble on deleting.
> > I would like to automaticly delete line in the 'coop' table when I dlete
> > the line in the 'fact' table but it is not ok.
> > Where is my mistake ?
>
> On delete cascade goes the other direction.  What you've written says that
> for any coop row that is deleted associated fact rows should be removed
> not the other way around.  AFAICT your schema allows multiple fact rows
> to reference a single coop row which means deleting the coop row will fail
> unless this is the only fact row.
>
> > This is my database:
> >
> > create table coop (
> > id_coop serial not null,
> > nom_coop varchar(50) not null,
> > primary key(id_coop)
> > );
> >
> > create table fact (
> > id_fact serial not null,
> > id_coop integer references coop(id_coop) on delete cascade,
> > date_fact timestamp not null,
> > montant_fact decimal(6,3),
> > primary key(id_fact)
> > );
> >
> > Thank's for your support.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On Wed, 27 Nov 2002, fred wrote:

> Ok,
> Thank you for your quick answer,
> I'm affraid I made a mistake on my mail.
> I really wanted to automaticly delete line in the 'fact' table when I delete
> a line in the 'fact' table.
> But when I suppress a line in the 'coop' table I still have the related line
> in the 'fact' table.
> Do you think there is a mistake in my sql syntaxe ?

Not really, and on my 7.3b2 system (should be pretty much the same as
7.2.3 for the integrity constraints, since the only change I remember
being made was pretty unrelated) a delete from coop deletes all the rows
from fact.

Can you give a complete example (including some inserts and deletes) so we
can try it?