Postgresql – How to add FK on a column of composite type in PostgreSQL

composite-typesforeign keypostgresql

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK.

CREATE TABLE aaa (col1 SERIAL PRIMARY KEY);

CREATE TYPE bbb AS (col2 INTEGER);
CREATE TABLE ccc (col3 bbb);

I tried to add FK but it was syntax error.

CREATE TABLE ccc (col3 bbb, FOREIGN KEY col3.col2 REFERENCES aaa col1);

ERROR:  syntax error at or near "col3"
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY col3.col2 REFERENCES…

This is also a syntax error.

CREATE TABLE ccc (col3 bbb, FOREIGN KEY (col3.col2) REFERENCES aaa (col1));

ERROR:  syntax error at or near "."
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY (col3.col2) REFERENC…

Also this…

CREATE TABLE ccc (col3 bbb, FOREIGN KEY ((col3).col2) REFERENCES aaa (col1));

ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY ((col3).col2) REFERE…

How can I add FK to a sub-column which is a column of a composite type?
I am not trying to make multi-column FK. I wan to link a single INTEGER column to another INTEGER column.

I am using PostgreSQL 9.3.

Update

This is simplified version of my current schema. I am essentially abstracting tables into hierarchical key-value set.

CREATE DOMAIN           "AlwaysSUID" AS BIGINT NOT NULL;
CREATE DOMAIN           "OptionalSUID" AS BIGINT DEFAULT NULL;

CREATE DOMAIN           "AlwaysString" AS TEXT NOT NULL;
CREATE DOMAIN           "OptionalString" AS TEXT DEFAULT NULL;

-- More similar domains...

CREATE TYPE             "Account" AS
(    
    "socialName"            "AlwaysString", 
    "mailAddress"           "AlwaysString", 
    "passcodeHash"          "AlwaysString"
);

CREATE TYPE             "Session" AS
(
    "accountID"             "AlwaysSUID",  -- This needs to be linked to `AccountMap.ID` as FK.
    "chracterID"            "AlwaysSUID",
    "initiationTime"        "AlwaysTimestamp",
    "deviceNameID"          "AlwaysString"              
);


CREATE TABLE            "AccountMap"
(
    "ID"                    BIGSERIAL PRIMARY KEY,
    "content"               "Account"
);

CREATE TABLE            "SessionMap"                    
(
    "ID"                    BIGSERIAL PRIMARY KEY,
    "content"               "Session"
);

Best Answer

This avoids the syntax error:

create type bbb as (col2 int);

create table aaa (
  col1 serial primary key
);

create table ccc (
  id serial primary key,
  col3 bbb references aaa --will reference the primary key of aaa
);

Even without the syntax error, you still get:

"Schema Creation Failed: ERROR: foreign key constraint "ccc_col3_fkey" cannot be implemented
  Detail: Key columns "col3" and "col1" are of incompatible types: bbb and integer.: "

This works though:

create type bbb as (col2 int);

create table aaa (
  col1 bbb primary key
);

create table ccc (
  id serial primary key,
  col3 bbb references aaa
);

But now you don't have a sequence. Also, you can't create sequence as...

So you are going to have to live without sequences on those tables or just use an int instead of a bbb.

Related Question