Re: foreign keys and transactions - Mailing list pgsql-general

My main concern is with a transaction being aborted by trying to delete a
row that still has a foreign key referencing it. In your example schema
below, I could check for foreign keys by just checking the "orders" and
"shipments" tables. However, if I then add a "notes" table, I would have
to go through all the code that tries to delete the row out of the
"person" table to make sure it checks the "notes" table for foreign keys
referencing that row. Even though I don't care if the delete statement
fails, I still have to worry about it aborting the entire transaction. It
just seems that there should be an easier way to check for foreign keys
or to prevent the delete from aborting the rest of the transaction.

-Edwin


On 8 Feb 2002, Jason Earl wrote:

>
> I am a little confused about what exactly you are trying to say, so if
> this comes out all wrong please forgive me.
>
> Edwin Grubbs <egrubbs@rackspace.com> writes:
>
> > Does anyone know a good solution to determining whether a row is
> > referenced by a foreign key? The problem is that multiple tables may
> > have foreign keys referencing a single table; therefore, even if you
> > delete a given foreign key from one table, the delete on the table
> > with the primary key may fail, which will cause the transaction to
> > abort. For example, a table of contacts might be referenced by
> > foreign keys in an account table, a log table, a group table, and an
> > employee table. If we delete an account, we want to try to delete
> > the contact, and if it fails we can go on our merry way because it
> > should just mean that it is referenced by another table. Querying
> > every single table that could possibly have a foreign key
> > referencing the contact seems error prone and a duplication of the
> > foreign key checks.
>
> Let's pretend you had a simple schema like this:
>
> CREATE TABLE people (
>         id              SERIAL NOT NULL PRIMARY KEY,
>         last_name       text,
>         first_name      text
> );
>
> CREATE TABLE orders (
>         id              SERIAL NOT NULL PRIMARY KEY,
>         customer        int REFERENCES people(id),
>         item            int
> );
>
> CREATE TABLE shipments (
>         id              SERIAL NOT NULL PRIMARY KEY,
>         customer        int REFERENCES people(id),
>         item            int
> );
>
> Now, clearly this is over-simplified and for educational purposes
> only.  With this particular arrangement deletes to the "people" table
> will fail if there are rows that reference the key in either
> "shipments" or "orders."  That's probably precisely what you want in
> this case, because you don't want to lose orders or lose track of your
> shipments.  However, let's say that for some reason you wanted to be
> able to remove people that had orders, but no shipments.  You could
> start a transaction with BEGIN, delete from orders where 'customer'
> was equal to a certain number and then delete from people where id was
> equal to that same number.  If that person had an entry in the
> shipments table then the delete from people would fail, and the
> transaction would get rolled back.  The SQL would look like this:
>
> BEGIN;
> DELETE FROM orders WHERE customer = <your_people.id>;
> DELETE FROM people WHERE id = <your_people.id>;
> COMMIT;
>
> If you *always* wanted to be able to delete folks from the people
> table who only had entries in the order table you could change the
> schema slightly so that it looked like this:
>
> CREATE TABLE orders (
>         id              SERIAL NOT NULL PRIMARY KEY,
>         customer        int REFERENCES people(id)
>                                 ON DELETE CASCADE,
>         item            int
> );
>
> Then you could remove people from both the people table and the orders
> table with a simple:
>
> DELETE FROM people WHERE id = <your_people.id>;
>
> Once again, this would do the right thing if the user only had entries
> in the orders table, and if the person had entries in the shipments
> table as well it would fail with an error.  The only real difference
>
> > I don't want to just have a separate transaction for each delete
> > from the table with the primary key, since that will require placing
> > all the deletes after the transaction which contains all the other
> > statements.  This would make it unbelievably difficult to use
> > functions in our code to handle related sql queries, since all the
> > deletes would have to be postponed till after the rest of the
> > transaction has finished.
> >
> > -Edwin Grubbs
>
> I hope that this was an example of what you were looking for.  If not,
> you might want to be a bit more specific.  And remember, transactions
> are your friends.
>
> Jason
>
>