Hi,
Just one other thing.
With the senario below it's possible to have addresses without a link to any Park.
Say if I remove a Park then it's row in the parks_address table still exists.
I guess I just need to write a script to look for lonely addresses ?
Does that sound OK ?
Cheers
Rudi.
Nabil Sayegh wrote:
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
