Kenneth Gonsalves <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
