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

but in table A , if I change it the PK to integer, everything would be fine.

any idea?

thanks a lot!

31 Aug 2002 05:28:53 -0700
leozc@cse.unsw.edu.au (Zhicong Leo Liang)
serial type as foreign key referential integrity violation
On 31 Aug 2002 at 5:28, 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)

that should be varchar..

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

this works.. I guess it's matter of writing a bit cleaner sql if nothing else. 
I am using postgresql-7.2-12mdk with mandrake8.2. 

I don't know which approach is better or correct, yours or mine. But this 
solves your problems at least..

test=# select * from a;
 a1  | a2
-----+----
 123 |
(1 row)

test=# insert into A(a2) values('123');
INSERT 4863345 1
test=# select * from a;
 a1  | a2
-----+-----
 123 |
   1 | 123
(2 rows)

test=# insert into b(b1,b2) values(1,1);
INSERT 4863346 1
test=# select * from a;
 a1  | a2
-----+-----
 123 |
   1 | 123
(2 rows)

test=# select * from b;
 b1 | b2
----+----
  1 |  1
(1 row)

test=#

Bye
 Shridhar

--
Concept, n.:	Any "idea" for which an outside consultant billed you more than	
$25,000.


Tue, 03 Sep 2002 19:21:32 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: serial type as foreign key referential integrity violation
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.



Tue, 3 Sep 2002 08:44:27 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: serial type as foreign key referential integrity