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.

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.