[SOLVED] PostgreSQL: Foreign key between composite type and independent columns

PostgreSQL: Foreign key between composite type and independent columns

Minimal definitions:

CREATE TYPE GlobalId AS (
  id1 BigInt,
  id2 SmallInt
);

CREATE TABLE table1 (
  id1 BigSerial NOT NULL,
  id2 SmallInt NOT NULL,
  PRIMARY KEY (id1, id2)
);

CREATE TABLE table2 (
  global_id GlobalId NOT NULL,
  FOREIGN KEY (global_id) REFERENCES table1 (id1, id2)
);

In short, I use a composite type for table2 (and many other tables), but for the primary table (table1), I don't directly use the composite type because composite types don't support the use of Serial.

The above produces the following error due to the ostensible mismatch between global_id and id1, id2: number of referencing and referenced columns for foreign key disagree.

Alternatively, if I define the foreign key as FOREIGN KEY (global_id.id1, global_id.id2) REFERENCES table1 (id1, id2), I get a syntax error on using an accessor on global_id.

Any ideas on how to define this foreign key relationship? Alternatively, if there's a way for table1 to use the GlobalId composite type while still getting serial/sequence behavior for id1, that works also.

Solution

You can define table1 using your composite type and fill the value using a BEFORE trigger:

CREATE TABLE table1 (id globalid PRIMARY KEY);

CREATE SEQUENCE s OWNED BY table1.id;

CREATE FUNCTION ins_trig() RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
   NEW.id = (nextval('s'), (NEW.id).id2);
   RETURN NEW;
END;$$;

CREATE TRIGGER ins_trig BEFORE INSERT ON table1 FOR EACH ROW
   EXECUTE PROCEDURE ins_trig();

INSERT INTO table1 VALUES (ROW(NULL, 42));

SELECT * FROM table1;
   id   
--------
 (1,42)
(1 row)