Postgres Pro
Downloads
Home   >   mailing lists

Thread: forcing a table (parent in inheritance) tor emain empty

forcing a table (parent in inheritance) tor emain empty

From
Rémi Cura
Date:
Hey dear list,
I can't find a nice solution to enforce a necessary behaviour in my case :
I want a parent table to remain empty.

Of course I could define a trigger and return NULL in any case, but I'd like a more elegant approach using check or constraints.

Any thought appreciated,
Cheers,
Rémi C

Re: forcing a table (parent in inheritance) tor emain empty

From
Manuel Gómez
Date:
On Tue, Oct 11, 2016 at 12:27 PM, Rémi Cura <remi.cura@gmail.com> wrote:
> Hey dear list,
> I can't find a nice solution to enforce a necessary behaviour in my case :
> I want a parent table to remain empty.
>
> Of course I could define a trigger and return NULL in any case, but I'd like
> a more elegant approach using check or constraints.

You could probably do it with a simple constraint:

postgres=# create table dum(check (false));
CREATE TABLE
postgres=# insert into dum default values;
ERROR:  new row for relation "dum" violates check constraint "dum_check"
DETAIL:  Failing row contains ().

Re: forcing a table (parent in inheritance) tor emain empty

From
Rémi Cura
Date:
This solution is very nice.
Sadly the check is inherited by the children
(I only want the parent to be empty, not the children).

It seems the element that are not inherited are
  • Indexes
  • Unique constraints
  • Primary Keys
  • Foreign keys
  • Rules and Triggers

thanks anyway for the fast answer,
Cheers,
Rémi C

2016-10-11 18:33 GMT+02:00 Manuel Gómez <targen@gmail.com>:
On Tue, Oct 11, 2016 at 12:27 PM, Rémi Cura <remi.cura@gmail.com> wrote:
> Hey dear list,
> I can't find a nice solution to enforce a necessary behaviour in my case :
> I want a parent table to remain empty.
>
> Of course I could define a trigger and return NULL in any case, but I'd like
> a more elegant approach using check or constraints.

You could probably do it with a simple constraint:

postgres=# create table dum(check (false));
CREATE TABLE
postgres=# insert into dum default values;
ERROR:  new row for relation "dum" violates check constraint "dum_check"
DETAIL:  Failing row contains ().

Re: forcing a table (parent in inheritance) tor emain empty

From
Julien Rouhaud
Date:
On 11/10/2016 19:04, Rémi Cura wrote:
> This solution is very nice.
> Sadly the check is inherited by the children
> (I only want the parent to be empty, not the children).
>
> It seems the element that are not inherited are
>
>   * Indexes
>   * Unique constraints
>   * Primary Keys
>   * Foreign keys
>   * Rules and Triggers
>

you can specify a "NO INHERIT" on the check constraint, that should
solve your issue.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

Re: forcing a table (parent in inheritance) tor emain empty

From
Rémi Cura
Date:
Perfect !

Cheers,
Rémi C

2016-10-11 19:12 GMT+02:00 Julien Rouhaud <julien.rouhaud@dalibo.com>:
On 11/10/2016 19:04, Rémi Cura wrote:
> This solution is very nice.
> Sadly the check is inherited by the children
> (I only want the parent to be empty, not the children).
>
> It seems the element that are not inherited are
>
>   * Indexes
>   * Unique constraints
>   * Primary Keys
>   * Foreign keys
>   * Rules and Triggers
>

you can specify a "NO INHERIT" on the check constraint, that should
solve your issue.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org