Skip site navigation (1) Skip section navigation (2)

Re: Creating related tables

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: Ângelo Marcos Rigo <angelo_rigo(at)yahoo(dot)com(dot)br>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Creating related tables
Date: 2005-10-19 13:09:53
Message-ID: 5340D02E-D976-45CC-BE95-246885E505B8@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Oct 19, 2005, at 21:34 , Ângelo Marcos Rigo wrote:

> Hi
>
> I need to create 1:N related tables .
>
> eg.: table A and a table B.
> - table B will have motorcicles
> - table A will have motorcicles owners
> If a owner is deleted from the table A i need to
> delete all the motorcicles with the deleted owner id
> in the B table.
>
> What is the sintax in postgesql todo this  ?


I believe you're looking for ON DELETE CASCADE. Please see below for  
an example.

Michael Glaesemann
grzm myrealbox com


Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

test=# create table motorcycle_owners (
      motorcycle_owner_id integer primary key
      , motorcycle_owner_name text not null
) without oids;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"motorcycle_owners_pkey" for table "motorcycle_owners"
CREATE TABLE
test=# create table motorcycles (
      motorcycle_id integer primary key
      , motorcycle_description text not null
      , motorcycle_owner_id integer not not null
          references motorcycle_owners (motorcycle_owner_id)
         on update cascade on delete cascade
) without oids;
ERROR:  syntax error at or near "not" at character 147
LINE 4:     , motorcycle_owner_id integer not not null
                                               ^
test=# create table motorcycles (
      motorcycle_id integer primary key
      , motorcycle_description text not null
      , motorcycle_owner_id integer not null
          references motorcycle_owners (motorcycle_owner_id)
         on update cascade on delete cascade
) without oids;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"motorcycles_pkey" for table "motorcycles"
CREATE TABLE
test=# copy motorcycle_owners (motorcycle_owner_id,  
motorcycle_owner_name) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
 >> 1    Tom
 >> 2    Bruce
 >> 3    Mark
 >> \.
test=# copy motorcycles (motorcycle_id, motorcycle_description,  
motorcycle_owner_id) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
 >> 1    Really cool bike        1
 >> 2    Really fast bike        2
 >> 3    Scooter 2
 >> 4    Really big bike 3
 >> \.
test=# select * from motorcycle_owners;
motorcycle_owner_id | motorcycle_owner_name
---------------------+-----------------------
                    1 | Tom
                    2 | Bruce
                    3 | Mark
(3 rows)

test=# select * from motorcycles;
motorcycle_id | motorcycle_description | motorcycle_owner_id
---------------+------------------------+---------------------
              1 | Really cool bike       |                   1
              2 | Really fast bike       |                   2
              3 | Scooter                |                   2
              4 | Really big bike        |                   3
(4 rows)

test=# select motorcycle_id, motorcycle_description
, motorcycle_owner_id, motorcycle_owner_name
from motorcycles
join motorcycle_owners using (motorcycle_owner_id);
motorcycle_id | motorcycle_description | motorcycle_owner_id |  
motorcycle_owner_name
---------------+------------------------+--------------------- 
+-----------------------
              1 | Really cool bike       |                   1 | Tom
              2 | Really fast bike       |                   2 | Bruce
              3 | Scooter                |                   2 | Bruce
              4 | Really big bike        |                   3 | Mark
(4 rows)

test=# delete from motorcycle_owners where motorcycle_owner_name =  
'Bruce';
DELETE 1
test=# select * from motorcycles;
motorcycle_id | motorcycle_description | motorcycle_owner_id
---------------+------------------------+---------------------
              1 | Really cool bike       |                   1
              4 | Really big bike        |                   3
(2 rows)




In response to

pgsql-novice by date

Next:From: Kevin CrenshawDate: 2005-10-19 13:23:51
Subject: Re: Creating related tables
Previous:From: A. KretschmerDate: 2005-10-19 13:06:49
Subject: Re: Creating related tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group