Thread: [GENERAL] foreign key with char and varchar

[GENERAL] foreign key with char and varchar

From

Thomas Poty

Date:

10 August 2017, 10:37:43

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

Re: [GENERAL] foreign key with char and varchar

From

Tom Lane

Date:

10 August 2017, 17:54:54

Thomas Poty <thomas.poty@gmail.com> writes:
> I wanted to test if char and varchar can be cross-referenced as foreign
> key. So i did these tests :
> ...
> 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.

Looking into the code, I see that the actual rules are that the FK
comparisons are done using the equality semantics of the referenced (PK)
column, so long as there is an implicit coercion available from the
referencing (FK) column type.  So the comparisons are done as though
by t6.id_t5::varchar = t5.id, which is perhaps a bit surprising
because if you just write "WHERE t6.id_t5 = t5.id" you would get the
opposite coercion, t6.id_t5 = t5.id::char.  (The first case will strip
trailing spaces from the char value but treat trailing spaces in the
varchar value as significant; the second case will consider trailing
spaces insignificant on both sides.)  But it more or less has
to be this way, because the foreign key constraint makes no sense
at all unless it has the same notion of equality as does the unique
index on the PK column.  Otherwise there could be more than one PK
row that "matches" an FK row.

If this is explained anywhere in the user-facing documentation,
I didn't find it in a quick look :-(

            regards, tom lane