Re: [HACKERS] serial type as foreign key referential integrity violation

2002-09-03 Thread Shridhar Daithankar 

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.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] serial type as foreign key referential integrity

2002-09-03 Thread Stephan Szabo 

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.



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html