Re: Feature Proposal: Constant Values in Columns or Foreign Keys - Mailing list pgsql-general

From Nils Gösche
Subject Re: Feature Proposal: Constant Values in Columns or Foreign Keys
Date
Msg-id 002e01cd1ce6$4cc076f0$e64164d0$@de
Whole thread Raw
In response to Re: Feature Proposal: Constant Values in Columns or Foreign Keys  (Bartosz Dmytrak <bdmytrak@gmail.com>)
Responses Re: Feature Proposal: Constant Values in Columns or Foreign Keys
Re: Feature Proposal: Constant Values in Columns or Foreign Keys
List pgsql-general
Bartosz Dmytrak wrote:

> how about inheritance in postgres?

I know about Postgres' inheritance feature, but would prefer a more standard relational solution.

> With this approach all IDs will use the same sequence so there will not be duplicated PKs in inherited tables.

In my case, the primary keys are usually uuids, actually. It was just an example, anyway.

> This could be also modeled with "standard" SQL approach without redundant information. Solution depends on
requirements.

Well, it could be modeled in the way I described, if only I could use constant values in foreign keys:

  FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE CASCADE

I was quite surprised to find that this wasn't possible. Is there any good reason why not?

The reason I like this particular way of modeling the data is that I have a guarantee that there won't be an entry in
bothderived tables at the same time for the same row in the base table; also, I can have further constraints and
foreignkeys from and to the base table. 

Of course, I could also omit the type field and simply live with the possibility of having two rows in the derived
tablesreferring to the same row of the base table. But it would be nice if I could rule that out with simple
constraints.

Regards,
--
Nils Gösche
Don't ask for whom the <Ctrl-G> tolls.


Attachment

pgsql-general by date:

Previous
From: Michael Nolan
Date:
Subject: Re: LOCK TABLE is not allowed in a non-volatile function
Next
From: Tom Lane
Date:
Subject: Re: Feature Proposal: Constant Values in Columns or Foreign Keys