Re: Creating related tables

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)