In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one?
Suppose I have
CREATE TABLE main (
-- a and b are separate fields
a INTEGER,
b INTEGER,
PRIMARY KEY (a,b)
);
CREATE TYPE abtype AS (
a INTEGER,
b INTEGER
);
CREATE TABLE sub (
-- a and b are components of a composite type
ab abtype,
PRIMARY KEY (ab)
);
How do I set up a foreign key constraint specifying that sub.ab
references main.(a,b)
?
The following attempts are all syntactically wrong.
ALTER TABLE sub ADD CONSTRAINT sub_ab_fkey FOREIGN KEY
... (ab) REFERENCES main(a,b);
... (ab) REFERENCES main((a,b));
... (ab) REFERENCES main((a,b)::abtype);
... (ab) REFERENCES main(a,b)::abtype;
... ((ab).a,(ab).b) REFERENCES main(a,b);
... (ab.a,ab.b) REFERENCES main(a,b);
main
is a long existing table where any change would break too much, whereas the composite type is the more convenient approach in newer tables.