Problems with foreign key having different type
От | Mirko Zeibig |
---|---|
Тема | Problems with foreign key having different type |
Дата | |
Msg-id | 20010124164014.A23815@picard.inka.de обсуждение исходный текст |
Ответы |
Re: Problems with foreign key having different type
|
Список | 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
В списке pgsql-general по дате отправления: