How to prevent inserts in the parent table?

Asked 2012-03-03T11:32:27.803

There is a table t which has inherited children. I want that only the children can receive inserts. What is the best way to force that the parent table rejects inserts?

create table t (c int);
create table t1 () inherits (t);

This should not be possible:

insert into t (c) values (1);

EDIT:

I found a model visible solution in addition to the one from @wildplasser:

create table tfk (c integer unique check(false));
create table t (c integer, foreign key (c) references tfk(c));

Now it is not possible to insert into t UNLESS it is a null value, and still possible to insert into its children. It can be a good solution if that columnn is already constrained as not null but not enough otherwise. Or does someone know a trick to make the above work for null values?

News:

I asked for a new syntax in the postgresql list and it was done for 9.2:

Allow CHECK constraints to be declared NO INHERIT (Nikhil Sontakke, Alex Hunsaker)

This makes them enforceable only on the parent table, not on child tables.

Edit Report

2 Answers

create table t (c int, check (false) no inherit);

This will prevent inserts into table t. It adds a constraint that will never be true, so no data can be inserted. no inherit will prevent that constraint from affecting child tables.

answered 2013-05-30T03:03:51.493

This is ugly, but it appears to work:

--SET search_path='tmp';

DROP TABLE dontinsert CASCADE;
CREATE TABLE dontinsert
        ( id INTEGER NOT NULL PRIMARY KEY
        );

DROP TABLE doinsert CASCADE;
CREATE TABLE doinsert ()
        INHERITS (dontinsert)
        ;

CREATE RULE dont_do_it AS
        ON INSERT TO dontinsert
        DO INSTEAD NOTHING
        ;

INSERT INTO dontinsert(id) VALUES( 13) ;

INSERT INTO doinsert(id) VALUES( 42) ;

SELECT id AS id_from_dont FROM dontinsert;

SELECT id AS id_from_do FROM doinsert;

Result:

SET
NOTICE:  drop cascades to table doinsert
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dontinsert_pkey" for table "dontinsert"
CREATE TABLE
ERROR:  table "doinsert" does not exist
CREATE TABLE
CREATE RULE
INSERT 0 0
INSERT 0 1
 id_from_dont 
--------------
           42
(1 row)

 id_from_do 
------------
         42
(1 row)

UPDATE: since the OP wants INSERTSs to fail with a lot of noise, I had to add a canary-table with an impossible constraint imposed on it:

DROP TABLE alwaysempty CASCADE;
CREATE TABLE alwaysempty
        ( id INTEGER NOT NULL
        );

ALTER TABLE alwaysempty
        ADD CONSTRAINT dont_insert_you_sucker CHECK (id > 0 AND id < 0)
        ;

CREATE RULE dont_do_it AS
        ON INSERT TO dontinsert
        DO INSTEAD -- NOTHING
        INSERT INTO alwaysempty (id)
        VALUES (NEW.id)
        ;

The new output:

SET
NOTICE:  drop cascades to table doinsert
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dontinsert_pkey" for table "dontinsert"
CREATE TABLE
ERROR:  table "doinsert" does not exist
CREATE TABLE
DROP TABLE
CREATE TABLE
ALTER TABLE
CREATE RULE
ERROR:  new row for relation "alwaysempty" violates check constraint "dont_insert_you_sucker"
INSERT 0 1
 id_from_dont 
--------------
           42
(1 row)

 id_from_do 
------------


                        
                        
                            answered 2012-03-03T15:51:28.530



        
        
            Your Answer