Hi;
I was noticing that when storing nested data in PostgreSQL, that both
CHECK and NOT NULL constraints are not fired.
It seems like this is a case where inheritance provides a cleaner way
to incorporate re-usable data structures (with internal integrity
enforcement and method mapping) in the database (and I am thinking
that nested tables might be something largely relegated to views), so
I don't see this as a big deal at all. But it did puzzle me at first.
I guess I kinda see the logic in it right now.
As an aside, the more I delve into table inheritance, the more amazing
and useful it actually is (warts and all), and multiple inheritance
(which to my knowledge is only supported by PostgreSQL) turns this
into something I expect to use a lot more of in the future. I will
probably send a second email out at some point with my thoughts on
this.
I guess my major reason for asking is wondering if this is behavior
that is expected to change in the future or if the idea that table
constraints are only enforced on the named table is something that is
likely to change.
Here is a minimal example case:
or_examples=# create table test.typetest (id int not null, check (id > 0));
CREATE TABLE
or_examples=# create table test.tabletest (test test.typetest);
CREATE TABLE
or_examples=# insert into test.tabletest values (row(-1));
INSERT 0 1
or_examples=# insert into test.tabletest values (row(null));
INSERT 0 1
To do this, I have to (after deleting rows):
or_examples=# alter table test.tabletest add check ((test).id is not
null and (test).id > 0);
or_examples=# select * from test.tabletest;
test
------
(-1)
()
(2 rows)
To do this, I have to (after deleting rows):
or_examples=# alter table test.tabletest add check ((test).id is not
null and (test).id > 0);
or_examples=# insert into test.tabletest values (row(null));ERROR:
new row for relation "tabletest" violates check constraint
"tabletest_test_check"
or_examples=# select version()
or_examples-# ;
version
---------------------------------------------------------------------------------
--------------------------
PostgreSQL 9.1.4 on i386-redhat-linux-gnu, compiled by gcc (GCC)
4.7.0 20120507
(Red Hat 4.7.0-5), 32-bit
(1 row)
