Thread: Foreign key constraint on sub-column of composite-type column

Hi,

I was trying to create foreign key constraints on a sub-column of a composite-type column, but couldn't find a way to
doit. After asking around on IRC, it seems like this isn't supported in PostgreSQL. 

I wanted to do something like:
create type profile as (account_id integer);
create table users (profile profile);
alter table users add constraint account_fk foreign key ((profile).account_id) references accounts;

Would this be a viable feature request?

--David

On 20 October 2012 06:57, David Lee <davidomundo@gmail.com> wrote:

> I was trying to create foreign key constraints on a sub-column of a composite-type column, but couldn't find a way to
doit. After asking around on IRC, it seems like this isn't supported in PostgreSQL.
 
>
> I wanted to do something like:
>
>         create type profile as (account_id integer);
>
>         create table users (profile profile);
>
>         alter table users add constraint account_fk foreign key ((profile).account_id) references accounts;
>
> Would this be a viable feature request?

We're currently trying to add FKs for arrays. Other more complex
constraints make a lot of sense, just as exclusion constraints make
sense for single table constraints.

I think its worth logging so we know somebody actually wants this,
thanks for your input.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services