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, 2019 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, 2019 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, 2019 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, 2019 at 11:16
  • 1
    Don't use composite types like this. There is some sound reasoning behind the first normal form in relational databases. Dec 13, 2019 at 11:43

1 Answer 1

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.