Re: Resolving of reference/foreign keys

Oliver,

RI takes care of all this if you set it up. Ie, when you create
the tables in your example...

create table a
(
    id       int primary key,
    txt      text,
);

create table b
(
    id         int primary key,
   tab_a_id   int
               REFERENCES a (id)
              ON DELETE NO ACTION
               ON UPDATE CASCADE
               DEFERRABLE,
    name       text
);

Basically you setup table b so that the RI ON ACTIONS tell the
database what's allowed and what's not. Above, I've set ON DELETE
to NO ACTION which means that if you delete a row from table a
which is a FK in table b, the delete will fail unless you also
delete the row in table b. I believe this is what you're looking for.

You don't need to write any special code then to test or count
references etc. Postgresql does it all for you. Check out the
docs for more info.

Tom.

On Tue, Mar 19, 2002 at 02:57:39PM +0100, Oliver Friedrich wrote:
> Hi,
> PostgreSQL supports foreign/reference keys. Is it possible to check with
> this references for existing entries that need an entry before delete it? A
> example:
>
> Table A has a field 'ID' and an field 'text', the table B has its own field
> 'ID', the field 'table_A_ID' and field 'name'. The field 'table_A_ID'
> references to the field 'ID' of table A. Now i want to delete an entray in
> table A, but only if there is no entry in table B that requiers this entry.
> One solution would be, if i make a SELECT on table B with the ID of table A,
> but if there are more than one referenced fields this would be ineffective.
> So i want to send a query to table A, that resolves all reference or just
> counts the amount of references. Is that possible?
>
> Thanks,
> Oliver
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs