Thread: Behaviour

Hi,

I have the following tables :
CREATE TABLE tournee (      no_tournee SERIAL PRIMARY KEY);

CREATE TABLE fab_tournee (      id SERIAL PRIMARY KEY,      id_fab INTEGER REFERENCES fabrication ON DELETE CASCADE,
 no_tournee INTEGER REFERENCES tournee ON DELETE CASCADE); 

When I insert a new record into 'fab_tournee' whereas the field
'no_tournee' doesnt exist in 'tournee', I have this message :

ERROR:  <unnamed> referential integrity violation - key referenced
from fab_tournee not found in tournee

but 'id' increments in 'fab_tournee'.

Is it a normal behaviour ?

*************** LOG ******************************
piid=> select * from fab_tournee ;id | id_fab | no_tournee
----+--------+------------
(0 lignes)

piid=> insert into fab_tournee (id_fab, no_tournee) values(2, 1);
ERROR:  <unnamed> referential integrity violation - key referenced from
fab_tour
nee not found in tournee
piid=> select * from tournee;no_tournee
------------
(0 lignes)

piid=> insert into tournee values (1);
INSERT 17355 1
piid=> insert into fab_tournee (id_fab, no_tournee) values(2, 1);
INSERT 17356 1
piid=> select * from fab_tournee ;id | id_fab | no_tournee
----+--------+------------ 2 |      2 |          1
(1 ligne)
**************************************

On 18 Jul 2002, [ISO-8859-1] St=E9phane Raimbault wrote:

> Hi,
>
> I have the following tables :
> CREATE TABLE tournee (
>        no_tournee SERIAL PRIMARY KEY);
>
> CREATE TABLE fab_tournee (
>        id SERIAL PRIMARY KEY,
>        id_fab INTEGER REFERENCES fabrication ON DELETE CASCADE,
>        no_tournee INTEGER REFERENCES tournee ON DELETE CASCADE);
>
> When I insert a new record into 'fab_tournee' whereas the field
> 'no_tournee' doesnt exist in 'tournee', I have this message :
>
> ERROR:  <unnamed> referential integrity violation - key referenced
> from fab_tournee not found in tournee
>
> but 'id' increments in 'fab_tournee'.
>
> Is it a normal behaviour ?

Yes.  SERIAL uses a sequence for doing its value choice (it's
effectively similar to an integer column with a default) and
sequences do not rollback with transactions (due to concurrency
concerns).  You can find more info about this in the archives.