Hi world,
I wanted to test if char and varchar can be cross-referenced as foreign
key. So i did these tests :
1) Can a varchar(7) reference a char(2) ? PostgreSQL accepts it
create table t1 (id char(2) primary key, data text);
create table t2 (id char(2) primary key, data text, id_t1 varchar(7)
references t1 (id));
2) Can a varchar(7) reference a char(7) ? PostgreSQL accepts it
create table t3 (id char(7) primary key, data text);
create table t4 (id char(7) primary key, data text, id_t3 varchar(7)
references t3 (id));
3) Can a char(2) reference a varchar(7) ? PostgreSQL accepts it.
create table t5 (id varchar(7) primary key, data text);
create table t6 (id char(7) primary key, data text, id_t5 char(2)
references t5 (id));
3.1) I am very surprised to be able do that :
-> insert into t5 (id,data) values ('1','toto');
It works that is normal
-> insert into t6 (id,data,id_t5) values ('1','toto','1');
It works and it is a suprise by knowing char are padded with spaces so
PostgreSQL would compare '1' with '1 ' ( 1 with a space) and return false.
Can you explain why it is working ???
3.2) I am very surprised to be able do that :
-> insert into t5 (id,data) values ('2 ','tata');
It works and it is normal
> insert into t6 (id,data,id_t5) values ('2','tata','2');
ERROR: insert or update on table "t6" violates foreign key constraint
"t6_id_t5_fkey"
DETAIL: Key (id_t5)=(2 ) is not present in table "t5".
It works and it is a suprise by knowing char are padded with spaces so
PostgreSQL would compare '2 ' with '2 ' ( 2 with a space) and return true.
Can you explain why it is working ???
4) Can a char(7) reference a varchar(7) ? PostgreSQL accepts it
create table t7 (id varchar(7) primary key, data text);
create table t8 (id varchar(7) primary key, data text, id_t7 char(7)
references t7 (id));
I thought the columns referring and referenced had to be the same data type
with the same length but it seems not to be the case.
Thanks for answers
Thomas
