pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign key to multiple tables depending on another column's value
3+ messages / 2 participants
[nested] [flat]
* foreign key to multiple tables depending on another column's value
@ 2013-05-29 13:58 Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
2013-05-29 15:51 ` Re: foreign key to multiple tables depending on another column's value Vick Khera <vivek@khera.org>
0 siblings, 1 reply; 3+ messages in thread
From: Rodrigo Rosenfeld Rosas @ 2013-05-29 13:58 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
Sorry, I wasn't sure what list I should be sending this question to...
I have a multi-tenant-like application. We have a fields tree that we
call a template, with something like this:
fields(id, parent_id, name)
And several other related tables. Since we started to support multiple
templates we created another schema per added template, each having the
same fields and related tables. Now I was asked to create another
special template that doesn't contain the values for those fields
(stored in separate tables in the regular schemas) and that would allow
us to map some field to some specific field on each of the aggregate
templates.
So, to exemplify, let's suppose we have regular templates "template1"
and "template2" and a special template "special1". This is what I want
to achieve:
I'll use the name (id, parent_id) notation.
template1 (table name is template1.fields):
- Target (1, null)
- Name (2, 1)
- Country (3, 1)
template2 (table name is template2.fields):
- Borrower (1, null)
- Name (12, 1)
- Country (13, 1)
special1 (table name is special1.fields):
- Company [maps to either Target or Borrower] (20, null)
- Name (21, 20)
- Country (22, 20)
So, now the idea is to map those fields using another table:
special1.mapped_fields(field_id, template_name, mapped_field_id), with
records like:
(20, 'template1', 1)
(20, 'template2', 1)
(21, 'template1', 2)
(21, 'template2', 12)
(22, 'template1', 3)
(22, 'template2', 13)
But the problem, as you have noticed is that I can't use a foreign key
with mapped_field_id, because the referenced table will depend on the
value of template_name.
I know I could use a trigger, or some check constraint maybe, to ensure
the field exists upon insert (or update), but I can't ensure the
database will become inconsistent in case I remove a mapped field from
the other schema.
Now I can finally explain my question: is it possible that I set some
sort of foreign key whose referenced table and column would depend on
the value of another column?
Thanks in advance,
Rodrigo.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: foreign key to multiple tables depending on another column's value
2013-05-29 13:58 foreign key to multiple tables depending on another column's value Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
@ 2013-05-29 15:51 ` Vick Khera <vivek@khera.org>
2013-05-29 16:14 ` Re: [GENERAL] foreign key to multiple tables depending on another column's value Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Vick Khera @ 2013-05-29 15:51 UTC (permalink / raw)
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>; +Cc: pgsql-sql@postgresql.org, pgsql-general <pgsql-general@postgresql.org>
On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com
> wrote:
> I know I could use a trigger, or some check constraint maybe, to ensure
> the field exists upon insert (or update), but I can't ensure the database
> will become inconsistent in case I remove a mapped field from the other
> schema.
>
> Now I can finally explain my question: is it possible that I set some sort
> of foreign key whose referenced table and column would depend on the value
> of another column?
>
The FK tests are basically triggers, but highly optimized.
That said, the way they enforce the integrity is by having a trigger on
both tables. So for your custom need here, you would want to put a trigger
on the referenced table to disallow deleting a value that is still
referenced, or do whatever appropriate action upon delete/update your
application needs.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [GENERAL] foreign key to multiple tables depending on another column's value
2013-05-29 13:58 foreign key to multiple tables depending on another column's value Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
2013-05-29 15:51 ` Re: foreign key to multiple tables depending on another column's value Vick Khera <vivek@khera.org>
@ 2013-05-29 16:14 ` Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rodrigo Rosenfeld Rosas @ 2013-05-29 16:14 UTC (permalink / raw)
To: Vick Khera <vivek@khera.org>; +Cc: pgsql-sql@postgresql.org, pgsql-general <pgsql-general@postgresql.org>
Em 29-05-2013 12:51, Vick Khera escreveu:
>
> On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas
> <rr.rosas@gmail.com <mailto:rr.rosas@gmail.com>> wrote:
>
> I know I could use a trigger, or some check constraint maybe, to
> ensure the field exists upon insert (or update), but I can't
> ensure the database will become inconsistent in case I remove a
> mapped field from the other schema.
>
> Now I can finally explain my question: is it possible that I set
> some sort of foreign key whose referenced table and column would
> depend on the value of another column?
>
>
> The FK tests are basically triggers, but highly optimized.
>
> That said, the way they enforce the integrity is by having a trigger
> on both tables. So for your custom need here, you would want to put a
> trigger on the referenced table to disallow deleting a value that is
> still referenced, or do whatever appropriate action upon delete/update
> your application needs.
>
Ok, thanks. I just wanted to be sure there wasn't some hidden feature of
PostgreSQL I wasn't aware of yet...
You know, I'm always learning something new on PG, so it worths trying
to ask first ;)
Cheers,
Rodrigo.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-05-29 16:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-29 13:58 foreign key to multiple tables depending on another column's value Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
2013-05-29 15:51 ` Vick Khera <vivek@khera.org>
2013-05-29 16:14 ` Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox