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.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
