Postgres Pro
Downloads
Home   >   mailing lists

Thread: Delete Cascade

Delete Cascade

From
"Marcelo Pereira"
Date:
Hi,
 
I am in trouble deleting rows in cascade.
 
I have a scheme like this:
 
create table author (author_cod integer primary key, author_name char(20));
create table book (book_cod integer primary key, book_autor integer references author(author_cod), book_title char(30));
 
I would like to delete a 'author' in the author table and also all your books in the book table.
 
When I run
 
delete from author where author_cod = <number>
 
I dont get the row deleted if there is a row referenced by in the book table.
How can I delete my rows in cascade ???
 
Thanks in advance,
 
Marcelo Pereira
Computer Programmer

Re: Delete Cascade

From
Karel Zak
Date:
On Mon, Mar 12, 2001 at 12:07:13PM -0300, Marcelo Pereira wrote:
> Hi,
>
> I am in trouble deleting rows in cascade.
>
> I have a scheme like this:
>
> create table author (author_cod integer primary key, author_name char(20));
> create table book (book_cod integer primary key, book_autor integer references author(author_cod), book_title
char(30));
>
> I would like to delete a 'author' in the author table and also all your books in the book table.

create table book (
    book_cod    integer primary key,
    book_autor    integer
            references author(author_cod)
            on delete cascade,
    book_title    char(30)
);


 What is bad on PG's docs? ...see:

[ CONSTRAINT name ] REFERENCES reftable [ ( refcolumn ) ]
    [ MATCH matchtype ]
    [ ON DELETE action ]
    [ ON UPDATE action ]
    [ [ NOT ] DEFERRABLE ]
    [ INITIALLY checktime ]


            Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: Delete Cascade

From
Stephan Szabo
Date:
Add ON DELETE CASCADE to the references in book.

On Mon, 12 Mar 2001, Marcelo Pereira wrote:

> Hi,
>
> I am in trouble deleting rows in cascade.
>
> I have a scheme like this:
>
> create table author (author_cod integer primary key, author_name char(20));
> create table book (book_cod integer primary key, book_autor integer references author(author_cod), book_title
char(30));
>
> I would like to delete a 'author' in the author table and also all your books in the book table.
>
> When I run
>
> delete from author where author_cod = <number>
>
> I dont get the row deleted if there is a row referenced by in the book table.
> How can I delete my rows in cascade ???