Обсуждение: [GENERAL] Schemas and foreign keys
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
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
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
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 >