Re: Composite type

On Mon, Jan 27, 2014 at 2:02 AM, antono124 <g(dot)antonopoulos000(at)gmail(dot)com> wrote:
> Lets say that we have 2 tables.
> Create Table "table1" Of "type1"
> Create Table "table2" Of "type2"
>
> I want to refer the first table in the second. I want to reference the whole
> table not only one field, so something like that:
>
> CREATE TYPE type2 AS OBJECT (
> var1 NUMBER,
> var2 REF type1
> )
>
> CREATE TABLE table2 OF type2 (
> PRIMARY KEY (Pk),
> FOREIGN KEY (fk) REFERENCES table1)
>
> Can i do something like this in Postgre?
It is possible to use multiple column names with defining a foreign key:
=# create table t1 (a int, b text, primary key (a, b));
CREATE TABLE
=# create table t2 (a int, b text, c text, foreign key (a, b)
references t1 (a, b));
CREATE TABLE
=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | text |
c | text |
Foreign-key constraints:
"t2_a_fkey" FOREIGN KEY (a, b) REFERENCES t1(a, b)

You could as well use some custom types if you do not want to
reference all the columns...
=# create type ty1 as (a int, b int);
CREATE TYPE
=# create table t1 (c ty1 primary key);
CREATE TABLE
=# create table t2 (d int primary key, e ty1 references t1 (c));
CREATE TABLE
=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+---------+-----------
d | integer | not null
e | ty1 |
Indexes:
"t2_pkey" PRIMARY KEY, btree (d)
Foreign-key constraints:
"t2_e_fkey" FOREIGN KEY (e) REFERENCES t1(c)

Regards,
--
Michael