Обсуждение: Creating related tables
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 ? Ângelo Marcos Rigo AMR Informática (51) 3348 0870 Rua Pe. Alois Kades 400/210 Porto Alegre /RS/Brasil http://amr.freezope.org angelo_rigo@yahoo.com.br _______________________________________________________ Promoção Yahoo! Acesso Grátis: a cada hora navegada você acumula cupons e concorre a mais de 500 prêmios! Participe! http://yahoo.fbiz.com.br/
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)
Try:
CREATE TABLE owners (
Ownerid SERIAL PRIMARY KEY,
Ownername TEXT
);
CREATE TABLE motorcycles(
Mid SERIAL PRIMARY KEY,
Mname TEXT,
Ownerid INTEGER REFERENCES owners(ownerid)
ON DELETE CASCADE
);
HTH,
Kevin
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ângelo Marcos Rigo
Sent: Wednesday, October 19, 2005 8:34 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Creating related tables
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 ?
Ângelo Marcos Rigo
AMR Informática
(51) 3348 0870
Rua Pe. Alois Kades 400/210
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br
_______________________________________________________
Promoção Yahoo! Acesso Grátis: a cada hora navegada você acumula cupons e
concorre a mais de 500 prêmios! Participe! http://yahoo.fbiz.com.br/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
am 19.10.2005, um 12:34:20 +0000 mailte Ângelo Marcos Rigo folgendes: > 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 ? test=> create table b (id int primary key); CREATE TABLE test=> create table a (id int references b on delete cascade); CREATE TABLE test=> Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===