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

Re: Address Table

From: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
To: Rudi Starcevic <rudi(at)oasis(dot)net(dot)au>
Cc: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Address Table
Date: 2003-06-26 23:27:19
Message-ID: 1056670039.627.21.camel@billy (view raw or flat)
Thread:
Lists: pgsql-novice
Am Fre, 2003-06-27 um 01.08 schrieb Rudi Starcevic:
> If each Parks has *one and one only* address
> is it better to store the address in the parks_table
> or build another table just for Parks addresses - parks_address ?

1. more than 1 park can be at 1 address
2. name of a street can change over time

=> 2nd table for addresses

> If I have two tables would you put the parks_table primary key in the
> parks_address table or the other way round ie. the parks_address primary 
> key in the parks_table ?

same like above, many parks can be at the same location.

parks_table 'gets the PRIMARY KEY' from parks_address
We speak of a so called "FOREIGN KEY" and it "REFERENCES" parks_address.

e.g.:

CREATE TABLE parks_address
(
	id_parks_address SERIAL PRIMARY KEY,
	address text NOT NULL
);

CREATE TABLE parks_table
(
	id_parks_table SERIAL PRIMARY KEY,
	id_parks_address INT REFERENCES parks_address NOT NULL,
	park_name text NOT NULL
);

INSERT INTO parks_address (address) VALUES ('foo street');
INSERT INTO parks_address (address) VALUES ('bar street');

INSERT INTO parks_table (id_parks_address, park_name) VALUES (1,
'A Park in foo');
INSERT INTO parks_table (id_parks_address, park_name) VALUES (1,
'Another Park in foo');
INSERT INTO parks_table (id_parks_address, park_name) VALUES (2,
'A Park in bar');

If you want to DELETE all parks automatically when an address no longer
exists (e.g. an earthquake :) then you should write

REFERENCES parks_address ON DELETE CASCADE NOT NULL

instead.

If you mean, it's impossible for an address to disappear suddenly :)

then you could write:

REFERENCES parks_address ON DELETE RESTRICT NOT NULL

AFAIK this is the default.

HTH
-- 
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de


In response to

Responses

pgsql-novice by date

Next:From: M SpreijDate: 2003-06-27 00:22:21
Subject: test
Previous:From: Rudi StarcevicDate: 2003-06-26 23:08:13
Subject: Address Table

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