Thread: foreign key pointing to diff schema?

Hi all. Can i make a FK who points a table in a different schema? Or this
is implemented via a trigger by my own?

Thanks!
Gerardo

On 8/10/07, gherzig@fmed.uba.ar <gherzig@fmed.uba.ar> wrote:
> Hi all. Can i make a FK who points a table in a different schema? Or this
> is implemented via a trigger by my own?

Sure.  just prefix the table name with the schemaname and a .

create schema abc;
alter user me set search_path='abc', 'public';
create table z1 (id int primary key);\d z1       Table "abc.z1"Column |  Type   | Modifiers
--------+---------+-----------id     | integer | not null
Indexes:   "z1_pkey" PRIMARY KEY, btree (id)

(Note the abc.z1 there)
create schema test3;create table test3.z2 (id int primary key, z1id int references abc.z1(id));
\d test3.z2      Table "test3.z2"Column |  Type   | Modifiers
--------+---------+-----------id     | integer | not nullz1id   | integer |
Indexes:   "z2_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:   "z2_z1id_fkey" FOREIGN KEY (z1id) REFERENCES z1(id)


basically, schemas are just distinct name spaces.

> On 8/10/07, gherzig@fmed.uba.ar <gherzig@fmed.uba.ar> wrote:
>> Hi all. Can i make a FK who points a table in a different schema? Or
>> this
>> is implemented via a trigger by my own?
>
> Sure.  just prefix the table name with the schemaname and a .
>
> create schema abc;
> alter user me set search_path='abc', 'public';
> create table z1 (id int primary key);
>  \d z1
>         Table "abc.z1"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  id     | integer | not null
> Indexes:
>     "z1_pkey" PRIMARY KEY, btree (id)
>
> (Note the abc.z1 there)
> create schema test3;
>  create table test3.z2 (id int primary key, z1id int references
> abc.z1(id));
> \d test3.z2
>        Table "test3.z2"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  id     | integer | not null
>  z1id   | integer |
> Indexes:
>     "z2_pkey" PRIMARY KEY, btree (id)
> Foreign-key constraints:
>     "z2_z1id_fkey" FOREIGN KEY (z1id) REFERENCES z1(id)
>
>
> basically, schemas are just distinct name spaces.
>
Im sory, that was just a matter of trying and see, isnt? I guess i need a
beer :) Thanks for the example and the explanation!

Gerardo