Polymorphic delete help needed - Mailing list pgsql-general

Instead of holding just an id as a foreign key, the record holds a "type" field which is a string and an id.  The string is the name of the table to which the id applies.  (That is slightly simplified).

The first problem that creates is it makes it hard to do a constraint on the name/id pair.  I thought about writing a function that would take the pair and search the appropriate table.  If it found a match, it would return true and if not, it would return false.  I have not done that because the string used to "name" the table has been modified to look like a class name.  So, a foreign key pointing to the table happy_people would have "HappyPeople" in the string (and not "happy_people").  It is not an impossible task to transform the string but I just have not attacked it yet for a couple of reasons.

One reason is that I can put this check into Rails much easier.  I don't know which would be faster to execute or if it would make any significant different.

But I have a much bigger problem.  One that I can not really visualize how to properly solve and that is how do I do deletes.

To back up a step, I have a table called relationships which has two polymorphic foreign keys in it call parent and child.  Then I have a set of tables such as people, companies, addresses, etc.  Collectively, I call these items.

The relationships are the glue that point between items like a person and a company for example.

Now, suppose I want to delete a person.  That implies that some relationships pointing to that person are no longer valid.  If I remove those, that could imply that there are other items that now have no relationships pointing to them.  How should I delete the item, extra relationships, and extra items and still make this update so that if something fails in the middle, it will get properly rolled back?

Thank you for your help,

Perry Smith ( pedz@easesoftware.com )