Schemas and foreign keys

Am 21.07.2017 um 14:58 schrieb marcelo:

Recently I asked regarding schemas, and received very useful answers.
I conclude that I can put some tables in one schema and left others in
the public one.
If my app selects some schema, the backend will found automatically
the absent tables in "public".
So far, so good.
But what about foreign keys? At least, I will have foreign keys from
the tables in the specified schema to the tables in "public", because
I'm thinking that the tables in "public" would be references, while
the tables residing in the specified schema will be the transactional
ones.
TIA
Marcelo

that's no problem:

test=# create schema demo1;
CREATE SCHEMA
test=*# create schema demo2;
CREATE SCHEMA
test=*# create table master_table(id int primary key);
CREATE TABLE
test=*# create table demo1.demo_table(id int primary key, master_id int
references public.master_table);
CREATE TABLE
test=*# create table demo2.demo_table(id int primary key, master_id int
references public.master_table);
CREATE TABLE

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

marcelo wrote:

Recently I asked regarding schemas, and received very useful answers. I
conclude that I can put some tables in one schema and left others in the
public one.
If my app selects some schema, the backend will found automatically the
absent tables in "public".
So far, so good.
But what about foreign keys? At least, I will have foreign keys from the
tables in the specified schema to the tables in "public", because I'm
thinking that the tables in "public" would be references, while the
tables residing in the specified schema will be the transactional ones.

That shouldn't be a problem; schemas are just namespaces (with the option
to exclude users through permissions).

Try it and report back if you encounter problems.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you, Andreas.
Your answer closes this thread.

On 21/07/17 11:07, Andreas Kretschmer wrote:

Am 21.07.2017 um 14:58 schrieb marcelo:

Recently I asked regarding schemas, and received very useful answers.
I conclude that I can put some tables in one schema and left others
in the public one.
If my app selects some schema, the backend will found automatically
the absent tables in "public".
So far, so good.
But what about foreign keys? At least, I will have foreign keys from
the tables in the specified schema to the tables in "public", because
I'm thinking that the tables in "public" would be references, while
the tables residing in the specified schema will be the transactional
ones.
TIA
Marcelo

that's no problem:

test=# create schema demo1;
CREATE SCHEMA
test=*# create schema demo2;
CREATE SCHEMA
test=*# create table master_table(id int primary key);
CREATE TABLE
test=*# create table demo1.demo_table(id int primary key, master_id
int references public.master_table);
CREATE TABLE
test=*# create table demo2.demo_table(id int primary key, master_id
int references public.master_table);
CREATE TABLE

Regards, Andreas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general