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

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