3

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"
);
  • I would like to abstract complex columns into single unit. If it's not for some abstraction, what's the purpose of it? – Eonil Oct 28 '13 at 23:51
1

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.

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.