Re: Incomprehensible behaviour of a foreign key.

On Sun, 2003-07-20 at 07:34, Nigel J. Andrews wrote:
> I'm completely baffled by this thing, the work it is for is extremely urgent
> and this is currently a show stopper. My minimal test script showing the
> problem is attached and the output is shown below.
[[snip]
>
>
> **** Start by showing the problem table is empt _before_ the transaction starts
> select * from site_membership;
> id | site_id | group_id
> ----+---------+----------
> (0 rows)
[snip]
> psql:/tmp/aa2.sql:101: ERROR: $2 referential integrity violation - key in groups still referenced from site_membership

Is there actually a FK referring to these tables? Can you drop it
and see if your txn works, then recreate it, or even recreate
site_membership?

What if the FK doesn't like NULLs, which it would get, since
site_membership is empty? Maybe this would help, presuming all
fields are scalar:
INSERT INTO SITE_MEMBERSHIP VALUES (-1, -1, -1);

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+