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.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+