From: | Mirko Zeibig <mirko(at)picard(dot)inka(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problems with foreign key having different type |
Date: | 2001-01-24 15:40:14 |
Message-ID: | 20010124164014.A23815@picard.inka.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-01-24 15:43:33 | Re: [General] Comments |
Previous Message | Bruce Momjian | 2001-01-24 15:37:15 | Re: pg_logs and pg_variables Files |