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)