From: | Rudi Starcevic <rudi(at)oasis(dot)net(dot)au> |
---|---|
To: | |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Address Table |
Date: | 2003-06-27 00:26:19 |
Message-ID: | 3EFB8F2B.4040800@oasis.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mendola Gaetano | 2003-06-27 00:29:48 | Re: authentication questions |
Previous Message | M Spreij | 2003-06-27 00:22:21 | test |