Re: serial type as foreign key referential integrity

On 31 Aug 2002, Zhicong Leo Liang wrote:

> Hi all,
> Just briefly describe my problem.
> I have two tables.
> create table A(
> a1 serial primary key,
> a2 varchars(10)
> );
> create table B(
> b1 integer primary key,
> b2 Integer,
> foreign key(b2) references a(a1)
> )
> insert into A values('123');
> select a1 from A where a2='123'
> >--
> >a1
> >--
> >1
> >--

Did you actually do that sequence and get that result?
Because you shouldn't. That should have put a 123 in a1 and
a NULL in a2.
Perhaps you meant insert into a(a2) values('123');

> insert into B values (1,1);
> ERROR!! referential integrity violation - key referenced from B not found in A.

In any case doing the above (with correction) and the insert
works fine for me. We'll need more info.