Thread: Problems with foreign key having different type

Hello,

I created two tables:

--- snip ---
DROP TABLE pge;
DROP TABLE lnk;
DROP SEQUENCE pge_pge_id_seq;
CREATE TABLE pge (
  pge_id     SERIAL,
  pge_path   CHAR(255) CONSTRAINT ak_pge_path UNIQUE,
  PRIMARY KEY (pge_id)
);

CREATE TABLE lnk (
  lnk_pge_id    INTEGER,
  lnk_trg_path    VARCHAR(255) DEFAULT '/adm/missingpage'
);

ALTER TABLE lnk ADD CONSTRAINT fk_lnk_pge_id FOREIGN KEY (lnk_pge_id)
   REFERENCES pge(pge_id)
   ON DELETE CASCADE;
ALTER TABLE lnk ADD CONSTRAINT fk_lnk_trg_path FOREIGN KEY (lnk_trg_path)
   REFERENCES pge(pge_path)
   ON DELETE SET DEFAULT
   ON UPDATE CASCADE;

INSERT INTO pge (pge_path) VALUES ('/');
INSERT INTO pge (pge_path) VALUES ('/adm');
INSERT INTO pge (pge_path) VALUES ('/adm/missingpage');
INSERT INTO pge (pge_path) VALUES ('/products');
--- snap ---

This will not succeed:
INSERT INTO lnk VALUES ('1', '/adm');
ERROR:  Unable to identify an operator '=' for types 'bpchar' and 'varchar'
        You will have to retype this query using an explicit cast

I had a hard time to get rid of this, as I had created a trigger_on_delete
for pge, which will prevent deletion of page 1! Before my lnk-Table had
lnk_trg_id which would be a FK of pge.pge_id as well, after doing some drop
and create, I was not able to delete anything from pge, as there seemed to
remain references to the no-more existing lnk-table nonetheless.

May constraint should check for the equal datatypes of both keys.

Best Regards
Mirko

> [snipped foreign keys set up across non-comparable types]
>
> This will not succeed:
> INSERT INTO lnk VALUES ('1', '/adm');
> ERROR:  Unable to identify an operator '=' for types 'bpchar' and 'varchar'
>         You will have to retype this query using an explicit cast
>
> I had a hard time to get rid of this, as I had created a trigger_on_delete
> for pge, which will prevent deletion of page 1! Before my lnk-Table had
> lnk_trg_id which would be a FK of pge.pge_id as well, after doing some drop
> and create, I was not able to delete anything from pge, as there seemed to
> remain references to the no-more existing lnk-table nonetheless.
>
> May constraint should check for the equal datatypes of both keys.

Yeah, I've been working on a patch for that, but every machine I install
the postgres source to work on seems to either crash or fall off the net.
What I've been planning to do is make sure that an = operator exists for
each pair of keys and not allow the constraint otherwise.  One possibility
to get you out of this might be to define an = operator for bpchar and
varchar.