antono124 <g.antonopoulos000@gmail.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?
First, it's PostgreSQL or Postgres for short; not Postgre.
It's pretty hard to see what you want here. You might be looking
for something like this:
CREATE TYPE type1 AS (
k1 bigint,
v1 text
);
CREATE TYPE type2 AS (
k2 bigint,
v2 text,
k1 bigint
);
CREATE TABLE table1 (
LIKE type1,
PRIMARY KEY (k1)
);
CREATE TABLE table2 (
LIKE type2,
PRIMARY KEY (k2),
FOREIGN KEY (k1) REFERENCES table1
);
To reference data from both together you might want a view:
CREATE VIEW view1 AS
SELECT * FROM table1 JOIN table2 USING (k1);
If that's not quite what you're after you might want to look at the
INHERITS clause of CREATE TABLE.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company