Ragnar <gnari@xxxxxxx> writes: > On sun, 2007-06-24 at 09:54 +0000, danmcb wrote: >> Say I have a table, say my_table, that is self-referencing. ... >> in other words: the row pointed to by orig_id cannot reference any row >> other than itself. >> How might I implement this as a constraint? > you can get around the limitation that subqueries are not allowed in > CHECK constraints by using a function. In general that's a last-ditch measure that's best avoided. It's got two serious problems: 1. You've got to write explicit code for both ends of the constraint; for example, prevent a row from being changed to have orig_id != id if there are any rows linking to it. (And a check constraint cannot act at row deletion at all, so you'll still need the foreign key constraint to prevent deletion of a referenced row.) 2. There is no way to defend against contradictory concurrent updates, since neither check constraint can "see" uncommitted changes of other transactions. (Hmm ... actually you can probably work around that with suitable use of SELECT FOR UPDATE or SELECT FOR SHARE, not plain SELECT, in the checking function. But it's a whole 'nother layer of complexity for you to deal with.) The good thing about foreign key constraints is that those problems are already solved for you. So frequently the best advice for someone who's thinking of doing something like this is "redesign your schema so you don't need to". regards, tom lane
