Re: Resolving of reference/foreign keys

Hi Tom,
Thanks for your answer, but i think i have not clearly said what i want :)

I know, that Postgres has the capabilities to make shure that i can't delete
entries that a referenced by another one or i can delete the referencing
entries, etc ...

But i have normally the problem, that a sql exception is thrown if i try to
delete an entry, that is already in use/referenced by another entry. Because
i use a existing library to access the database i can't change the handling
for the sql exceptions. So i must test for the references before i try to
delete...

And instead of testing by my own all tables, that could use the entry i want
to delete i want to ask postgres if there are refernces to this entry. Par
example i have a table, that is in use by 16 other tables. And i don't want
to test all the 16 tables by my own... Perhaps this are later 23 tables or
99 tables... etc. There must be a more comfortable way to do this ...

Thanks,
Oliver

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Thomas O'Dowd
Sent: Wednesday, March 20, 2002 1:08 AM
To: Oliver Friedrich
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html