pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding if a foreign key is in use
6+ messages / 4 participants
[nested] [flat]
* finding if a foreign key is in use
@ 2004-06-29 00:48 Kenneth Gonsalves <lawgon@thenilgiris.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kenneth Gonsalves @ 2004-06-29 00:48 UTC (permalink / raw)
To: pgsql-sql
in my app i have a table where the id serves as a foreign key for one or more
other tables. if i want to delete a row in the table, i currently search the
other tables where this table is referenced to see if the row is in use - and
then, if not in use, permit deletion. Now if i want the delete button in my
app to be disabled whenever a row that is in use is selected, searching the
database every time would dramatically slow down the app. is there some sql
query that will immediatly give an answer as to whether the row is in use or
not?
--
regards
kg
http://www.onlineindianhotels.net - hotel bookings reservations in over 4600
hotels in India
http://www.ootygolfclub.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding if a foreign key is in use
@ 2004-06-29 13:49 Phil Endecott <spam_from_postgresql_sql@chezphil.org>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
0 siblings, 1 reply; 6+ messages in thread
From: Phil Endecott @ 2004-06-29 13:49 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> in my app i have a table where the id serves as a foreign key for
> one or more other tables. if i want to delete a row in the table,
> i currently search the other tables where this table is referenced
> to see if the row is in use - and then, if not in use, permit
> deletion.
You can automate this check by declaring the foreign keys like this:
id some_type references other_table(id) on delete no action
The "on delete no action" bit means "if you try to delete the row in the referred-to table (where it is the primary key), cause an error. The alternative is "on delete cascade", which means that rows in the referring tables are deleted if the row that they refer to is deleted.
> Now if i want the delete button in my app to be disabled whenever
> a row that is in use is selected, searching the database every time
> would dramatically slow down the app.
Basically you do have to do this search. But it won't be too slow if you create an index on the foreign key.
I would probably do it like this. Here is the first table:
create table T1 (
id integer primary key,
x text
);
Here is the second table that refers to it:
create table T2 (
id integer references T1.id on delete no action,
y text
);
So that the searches can be efficient, we create an index:
create index T2_by_id on T2(id);
Now I would create a view that adds an extra column to T1, indicating whether any rows in T2 refer to it:
create view T1_v as
select *, id in (select id from T2) as cannot_delete
from T1;
But beware! It seems that this particular form DOESN'T use the index we've just created. On the other hand, this very similar one does:
create view T1_v as
select *, exists (select * from T2 where id=t.id) as cannot_delete
from T1 t;
Now, when you create your user interface, you can just look at the cannot_delete field to see whether the delete button should be enabled.
This should run in logarithmic time. If this isn't fast enough you could instead make cannot_delete a real column and have triggers on changes to T2 that change its value. But I wouldn't resort to this unless you are really desperate.
Regards,
--Phil.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding if a foreign key is in use
@ 2004-07-01 02:13 Kenneth Gonsalves <lawgon@thenilgiris.com>
parent: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 2 replies; 6+ messages in thread
From: Kenneth Gonsalves @ 2004-07-01 02:13 UTC (permalink / raw)
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; pgsql-sql
On Tuesday 29 June 2004 07:19 pm, Phil Endecott wrote:
> Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> > in my app i have a table where the id serves as a foreign key for
> > one or more other tables. if i want to delete a row in the table,
> > i currently search the other tables where this table is referenced
> > to see if the row is in use - and then, if not in use, permit
> > deletion.
> > Now if i want the delete button in my app to be disabled whenever
> > a row that is in use is selected, searching the database every time
> > would dramatically slow down the app.
>
> Basically you do have to do this search. But it won't be too slow if you
> create an index on the foreign key.
pity. thought postgres would have some function like 'in_use' to tell when a
row that is used as a foreign key is in actual use and hence cannot be
deleted. surely, in a database of millions of records, it wouldnt have search
them all to find if the row is in use? in my case the id field serves as a
foreign key in several tables
--
regards
kg
http://www.onlineindianhotels.net - hotel bookings reservations in over 4600
hotels in India
http://www.ootygolfclub.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding if a foreign key is in use
@ 2004-07-01 06:54 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2004-07-01 06:54 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; pgsql-sql
On Thu, 1 Jul 2004, Kenneth Gonsalves wrote:
> On Tuesday 29 June 2004 07:19 pm, Phil Endecott wrote:
> > Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> > > in my app i have a table where the id serves as a foreign key for
> > > one or more other tables. if i want to delete a row in the table,
> > > i currently search the other tables where this table is referenced
> > > to see if the row is in use - and then, if not in use, permit
> > > deletion.
>
> > > Now if i want the delete button in my app to be disabled whenever
> > > a row that is in use is selected, searching the database every time
> > > would dramatically slow down the app.
> >
> > Basically you do have to do this search. But it won't be too slow if you
> > create an index on the foreign key.
>
> pity. thought postgres would have some function like 'in_use' to tell when a
> row that is used as a foreign key is in actual use and hence cannot be
> deleted. surely, in a database of millions of records, it wouldnt have search
> them all to find if the row is in use? in my case the id field serves as a
Well, it's kind of a losing proposition either way.
One way (the one we take right now) involves checking rows on the fktable
on pk update/delete which is hoped to be fast if the user creates
appropriate indexes but has issues if that plan isn't fast.
The other would probably involve trying to keep track of known in_use.
There are some problems there however in knowing what that value should
be. For example after a delete of a row in the fktable, do you know what
in_use is on its referenced row without finding all things that reference
it and checking them (and what about seeing or locking stuff for
concurrent transactions?) You can use a known in_use vs unknown in_use vs
known not in use style value to get around that, but then I think there'd
still be issues in guaranteeing the correct semantics and I think rows
may often end up in unknown state at which point you scan anyway.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding if a foreign key is in use
@ 2004-07-01 09:36 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 0 replies; 6+ messages in thread
From: Achilleus Mantzios @ 2004-07-01 09:36 UTC (permalink / raw)
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
O kyrios Phil Endecott egrapse stis Jul 1, 2004 :
> >>>in my app i have a table where the id serves as a foreign key for
> >>>one or more other tables. if i want to delete a row in the table,
> >>>i currently search the other tables where this table is referenced
> >>>to see if the row is in use - and then, if not in use, permit
> >>>deletion.
> >>>Now if i want the delete button in my app to be disabled whenever
> >>>a row that is in use is selected, searching the database every time
> >>>would dramatically slow down the app.
There's an alternative approach to take.
Educate your users to be familiar with PostgreSQL error messages
(E.g.
ERROR: update or delete on "vessels" violates foreign key constraint "$1"
on "certificates"
)
> >>
> >>Basically you do have to do this search. But it won't be too slow if you
> >>create an index on the foreign key.
> >
> > pity. thought postgres would have some function like 'in_use' to tell when a
> > row that is used as a foreign key is in actual use and hence cannot be
> > deleted. surely, in a database of millions of records, it wouldnt have search
> > them all to find if the row is in use?
>
> It doesn't "search them all" if you have an index. If your database has
> a million records it needs to look at only 20 index entries, as 2^20 is
> about a million. (At least that's what I, naively, think it should do -
> anyone who knows more want to correct me?)
>
> --Phil.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
-Achilleus
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding if a foreign key is in use
@ 2004-07-01 09:57 Phil Endecott <spam_from_postgresql_sql@chezphil.org>
parent: Kenneth Gonsalves <lawgon@thenilgiris.com>
1 sibling, 1 reply; 6+ messages in thread
From: Phil Endecott @ 2004-07-01 09:57 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
>>>in my app i have a table where the id serves as a foreign key for
>>>one or more other tables. if i want to delete a row in the table,
>>>i currently search the other tables where this table is referenced
>>>to see if the row is in use - and then, if not in use, permit
>>>deletion.
>>>Now if i want the delete button in my app to be disabled whenever
>>>a row that is in use is selected, searching the database every time
>>>would dramatically slow down the app.
>>
>>Basically you do have to do this search. But it won't be too slow if you
>>create an index on the foreign key.
>
> pity. thought postgres would have some function like 'in_use' to tell when a
> row that is used as a foreign key is in actual use and hence cannot be
> deleted. surely, in a database of millions of records, it wouldnt have search
> them all to find if the row is in use?
It doesn't "search them all" if you have an index. If your database has
a million records it needs to look at only 20 index entries, as 2^20 is
about a million. (At least that's what I, naively, think it should do -
anyone who knows more want to correct me?)
--Phil.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-07-01 09:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-29 00:48 finding if a foreign key is in use Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-06-29 13:49 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-07-01 02:13 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-07-01 06:54 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-07-01 09:57 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-07-01 09:36 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox