"Perry Smith" <pedz@xxxxxxxxxxxxxxxx> writes: > Right now, it would be nice if I could get a check constraint to be deferred. > Its a long story. I want a circular constraint. The way things are set up > right now, it would be easy if I could defer my check constraint. I'm doing a > polymorphic relation. One direction is a simple reference a fixed table. The > other direction is a reference to table that changes based upon the type of > the item. I can do this check in a function which implies it is a check > constraint. The main problem with this is that check constraints which refer to other tables don't really work. Not to the degree of rigour that referential integrity checks maintain. Consider what happens if someone updates the record you're targeting but hasn't committed yet. Your check constraint will see the old version and pass even though it really shouldn't. It'll even pass if the update has committed but your query started before it did so. > The other option is to add deferred check constraints to PostgreSQL. I've > never looked at the PostgreSQL code but I like parsers, etc. How hard would > it be to add this to PostgreSQL and is it something of general interest or am > I somewhat lost in the woods? I suspect the reason they don't exist is precisely as above that they don't really make a lot of sense. If your check constraint can't usefully include queries on other tables then there's no reason to defer it. Your record isn't going to become acceptable later if it isn't now. But many people do put queries in check constraints with the caveats understood -- it's still useful if the referred-to data is basically static. So perhaps it would be useful. There is something in the standard called Assertions which I think are supposed to address this issue. But they're "hard" and I don't know if any database supports them. I wonder if we did whether anyone would find them useful. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
