Обсуждение: [GENERAL] Schemas and foreign keys

Поиск
Список
Период
Сортировка

[GENERAL] Schemas and foreign keys

От
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


Re: [GENERAL] Schemas and foreign keys

От
Andreas Kretschmer
Дата:

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



Re: [GENERAL] Schemas and foreign keys

От
Albe Laurenz
Дата:
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

Re: [GENERAL] Schemas and foreign keys

От
marcelo
Дата:
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
>