Thread: Resolving of reference/foreign keys
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
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
Thomas O'Dowd wrote:
> [...]
> 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.
Just a quick one: This deletion, must it be done in (1) one statement,
or can the delete operation be approved if (2) multiple delete
statements are issued within a transaction?
(1) DELETE FROM A, B WHERE B.table_A_ID = A.ID AND bla1 bla2
(2) BEGIN WORK
DELETE FROM A WHERE bla1
DELETE FROM B WHERE bla2
COMMIT
Are the above examples just rubbish, i.e. there are never any actual
cases where (2) can not be written as (1)?
/
Fredrik Wendt
On Wed, Mar 20, 2002 at 01:31:07AM +0100, Fredrik Wendt wrote: > Thomas O'Dowd wrote: > > > [...] > > 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. > > Just a quick one: This deletion, must it be done in (1) one statement, > or can the delete operation be approved if (2) multiple delete > statements are issued within a transaction? The DEFERRABLE keyword says that the RI check should wait until the end of a transaction before complaining. So the answer is both in this case. Its all in the docs. http://www.postgresql.org/idocs/index.php?sql-createtable.html Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
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