Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"
);
share|improve this question
2  
Can you explain why do you want to do this? What is the problem you are solving? –  A-K Oct 28 '13 at 21:05
1  
A column of a type is not a column of a table so you cannot define a FK on it. You can probably define a trigger or a rule to achieve this - but Alex's question is completely valid. –  dezso Oct 28 '13 at 21:23
    
@AlexKuznetsov I 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
    
@dezso Thanks. I will look for those features. –  Eonil Oct 28 '13 at 23:56
    
@Eonil This abstraction is not a bad idea, I use it myself sometimes. However, I've never seen a case where a FK would have had to be defined on these. Probably a good example from you could help us understand your problem. –  dezso Oct 29 '13 at 7:24

1 Answer 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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