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

Edwin Grubbs <egrubbs@rackspace.com> writes:

> 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

That leaves you with one of two options.  Either you code into your
application smarts about which tables might rely on "people" or you
simply set all of your tables with the appropriate ON DELETE setting.

For example, if you want to be able to blow away rows in people no
matter what rows might be referencing it in other tables make sure
that when you create your tables they all have appropriate ON DELETE
settings.  Like this:

CREATE TABLE notes (
        id              SERIAL NOT NULL PRIMARY KEY,
        customer        references people(id)
                                ON DELETE CASCADE,
);

Now you can delete every all the rows from people that you want, and
the corresponding notes will also disappear.  You can also use ON
DELETE NULL if you don't want to potentially blow away valuable
information.  If you do that to *every* table that references "people"
then you can delete from people and the referencing rows in the other
tables will simply disappear.

It sounds like you are trying to make things harder than they are.  If
you set all of your referencing tables to CASCADE deletes then
PostgreSQL will take care of all of the work of deleting those rows
that reference your foreign key.  So just add ON CASCADE DELETE to all
of your tables and be happy :).

Try it, you'll like it.

Jason

> 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
> >
> >