Inconsistent behavior of constraint inheritance

[OndrejZapletal]

During my experimentation with AgensGraph, I came across a possible issue with constraint inheritance. It looks as if only check_expr is inherited.

I've tried the following scenario:

1. create an Entity label that has uid property that should be unique.
2. create a NamedEntity label that inherits from Entity and additionally has required property name.
3. create multiple NamedEntity vertices with the same uid.

What I would expect is an error due to a conflict of uid properties. When I try to create NamedEntity vertex without uid property, it correctly throws ERROR: new row for relation "namedentity" violates check constraint "Entity has uid".

CREATE VLABEL Entity;
CREATE CONSTRAINT "Entity has uid" ON Entity ASSERT uid IS NOT NULL;
CREATE CONSTRAINT "Entity uid is unique" ON Entity ASSERT uid IS UNIQUE;

CREATE VLABEL NamedEntity inherits (Entity);
CREATE CONSTRAINT "NamedEntity has name" ON NamedEntity ASSERT name IS NOT NULL;

CREATE (e:NamedEntity {uid: 'urn:entity:1', name: 'entity1'});
CREATE (e:NamedEntity {uid: 'urn:entity:1', name: 'entity2'});

Following query throws ERROR: conflicting key value violates exclusion constraint "NamedEntity uid is unique" as expected:

CREATE VLABEL Entity;
CREATE CONSTRAINT "Entity has uid" ON Entity ASSERT uid IS NOT NULL;

CREATE VLABEL NamedEntity inherits (Entity);

CREATE CONSTRAINT "NamedEntity has name" ON NamedEntity ASSERT name IS NOT NULL;
CREATE CONSTRAINT "NamedEntity uid is unique" ON NamedEntity ASSERT uid IS UNIQUE;

CREATE (e:NamedEntity {uid: 'urn:entity:1', name: 'entity1'});
CREATE (e:NamedEntity {uid: 'urn:entity:1', name: 'entity2'});

Is this behavior by design? Is the uniqueness constrained not inherited intentionally?

Agens version: AgensGraph 2.1.0, based on PostgreSQL 10.4

[joefagan]

Ondrej,

It is not normal for INHERITS to propagate uniqueness. You will have to explicitly apply uniqueness constraints to the inherited labels.

Please let me know whether this addresses your issue regarding inheritance. From the PostreSQL documentation at https://www.postgresql.org/docs/9.5/ddl-inherit.html

All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses.
Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

[OndrejZapletal]

Thanks for the response, Joe. Your response addressed my concerns. It makes sense. I didn't realize that this also applies to PostgreSQL itself.