Actually, the situation is slightly more complicated. It's more like I
have tables A1, A2, and A3 each of which must have a corresponding row
in B. So each of A1, A2 and A3 has a BEFORE INSERT trigger that creates
a row in B and sets a FK in A1 (or A2 or A3). So I can't just use the
same PK in both the A tables and B.
PFC wrote:
I'm using Ruby on Rails and have two tables, A and B. Every row in A
needs a corresponding row in B. A also contains a FK pointing to B.
I created a before insert trigger on A that inserts a new row in B,
and sets the FK in A. This seems to be running fine.
So, A has a b_id field linking to B ?
If you need a 1-1 relationship, you could try instead to use the
same primary key in B than in A :
A : id SERIAL PRIMARY KEY
B : id INTEGER PRIMARY KEY (not SERIAL)
Then, AFTER INSERT trigger on A checks the value the sequence put in
A.id and inserts in B with this value as the PK.
Postgres has INSERT ... RETURNING which is a very clean and
elegant solution but Rails never heard about it...
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
