On Tue, 4 Jun 2002, Zhou, Lixin wrote:
> Give two tables A and B. B has a field that references A's primary key.
>
> For example:
>
> create table A(
> i int not null,
> s text,
> primary key(i));
>
> create table B(
> i int not null,
> s text,
> primary key(i),
> foreign key(i) references A(i));
>
> I like to create a trigger on table A. When a new row is inserted into A
> (ex: with i = 5), I like to have the trigger inserts a new row in table B
> whose field "i" has the same value as that of the A's (ex: i = 5).
>
> As I do this, the error message is something like: "referential integration
> violation - key referenced in B not found in A". This makes sense to me
> since at the time the trigger inserts in B, A's new row is not visible yet
> -- not committed yet.
Actually, I'd think that should work since it should be post statement
that the constraint runs. Can you send the full info on the tables and
triggers you were using?
As a workaround, you could see if making the constraint deferrable and
initially deferred works.