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);
Best Answer
As I pointed out in comments, you could add a new composite column in
main
table:And set a unique index:
Now you can reference your FK to this column:
db<>fiddle here