0

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);
9
  • 1
    Index in 1st table consists from 2 fields, whereas in 2nd table from 1 field. None tricks in constraint expression can help. You must alter one of PKs (or create additional index for referencing). – Akina Dec 13 '19 at 10:20
  • @Akina Thanks for the clarification. I already suspected that I'm asking too much. The problem is that 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. – gernot Dec 13 '19 at 10:27
  • 1
    I have tested a little... it seems that user-defined type is more complex than simple fields "concatenation, and its components needs additional function(s) for to assign/access them. – Akina Dec 13 '19 at 10:54
  • 1
    Maybe you cannot alter main PK, but maybe you could add a new composite column and use it a as reference for the other FK. – McNets Dec 13 '19 at 11:16
  • 1
    Don't use composite types like this. There is some sound reasoning behind the first normal form in relational databases. – Laurenz Albe Dec 13 '19 at 11:43
2

As I pointed out in comments, you could add a new composite column in main table:

CREATE TYPE abtype AS (
    a INTEGER,
    b INTEGER
);

CREATE TABLE main (
    -- a and b are separate fields
    a INTEGER,
    b INTEGER,
    ab abtype generated always as ((a,b)) stored,
    PRIMARY KEY (a,b)
);

CREATE TABLE sub (
    -- a and b are components of a composite type
    ab abtype,
    PRIMARY KEY (ab)
);

And set a unique index:

create unique index on main (ab);

Now you can reference your FK to this column:

ALTER TABLE sub ADD CONSTRAINT sub_ab_fkey FOREIGN KEY (ab) references main(ab);

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.