<para>

There must also be matching child-table constraints for all

<literal>CHECK</literal> constraints of the parent, except those

marked non-inheritable (that is, created with <literal>ALTER TABLEONLY</literal>)

marked non-inheritable (that is, created with <literal>ALTER TABLE... ADD CONSTRAINT ... NO INHERIT</literal>)

in the parent, which are ignored; all child-table constraints matched

must not be marked non-inheritable.

Currently

<para>

To add a check constraint only to a table and not to its children:

<programlisting>

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECKNO INHERIT(char_length(zipcode) = 5);

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

</programlisting>

(The check constraint will not be inherited by future children, either.)

</para>

[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]

{ NOT NULL |

NULL |

CHECK[ NO INHERIT ]( <replaceable class="PARAMETER">expression</replaceable> ) |

CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |

DEFAULT <replaceable>default_expr</replaceable> |

UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |

PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |

<phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase>

[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]

{ CHECK[ NO INHERIT ]( <replaceable class="PARAMETER">expression</replaceable> ) |

{ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |

UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |

PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |

EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |

</varlistentry>

<varlistentry>

<term><literal>CHECK[ NO INHERIT ]( <replaceable class="PARAMETER">expression</replaceable> )</literal></term>

<term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]</literal></term>

<listitem>

<para>

The <literal>CHECK</> clause specifies an expression producing a

<listitem>

<para>

Allow <literal>CHECK</> constraints to be declared <literal>NO

INHERIT</> (Nikhil Sontakke, Alex Hunsaker)

INHERIT</> (Nikhil Sontakke, Alex Hunsaker, &Aacute;lvaro Herrera)

</para>

<para>

if (constr->is_no_inherit)

ereport(ERROR,

(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),

errmsg("CHECK constraints for domains cannot be marked NO INHERIT")));

break;

#defineCAS_INITIALLY_IMMEDIATE0x04

#defineCAS_INITIALLY_DEFERRED0x08

#defineCAS_NOT_VALID0x10

#defineCAS_NO_INHERIT0x20

#defineparser_yyerror(msg) scanner_yyerror(msg, yyscanner)

core_yyscan_t yyscanner);

staticvoidprocessCASbits(int cas_bits,int location,constchar *constrType,

bool *deferrable,bool *initdeferred,bool *not_valid,

core_yyscan_t yyscanner);

bool *no_inherit,core_yyscan_t yyscanner);

n->indexspace =$4;

$$ = (Node *)n;

}

{

Constraint *n = makeNode(Constraint);

n->contype = CONSTR_CHECK;

n->location =@1;

n->is_no_inherit =$2;

n->raw_expr =$4;

n->is_no_inherit =$5;

n->raw_expr =$3;

n->cooked_expr =NULL;

$$ = (Node *)n;

}

ConstraintAttr:

;

ConstraintElem:

{

Constraint *n = makeNode(Constraint);

n->contype = CONSTR_CHECK;

n->location =@1;

n->is_no_inherit =$2;

n->raw_expr =$4;

n->raw_expr =$3;

n->cooked_expr =NULL;

processCASbits($6, @6,"CHECK",

processCASbits($5, @5,"CHECK",

NULL,NULL, &n->skip_validation,

yyscanner);

&n->is_no_inherit,yyscanner);

n->initially_valid = !n->skip_validation;

$$ = (Node *)n;

}

n->indexspace =$6;

processCASbits($7, @7,"UNIQUE",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

$$ = (Node *)n;

}

n->indexspace =NULL;

processCASbits($3, @3,"UNIQUE",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

$$ = (Node *)n;

}

n->indexspace =$7;

processCASbits($8, @8,"PRIMARY KEY",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

$$ = (Node *)n;

}

n->indexspace =NULL;

processCASbits($4, @4,"PRIMARY KEY",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

$$ = (Node *)n;

}

n->where_clause=$8;

processCASbits($9, @9,"EXCLUDE",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

$$ = (Node *)n;

}

n->fk_del_action= (char) ($10 &0xFF);

processCASbits($11, @11,"FOREIGN KEY",

&n->deferrable, &n->initdeferred,

&n->skip_validation,

&n->skip_validation,NULL,

yyscanner);

n->initially_valid = !n->skip_validation;

$$ = (Node *)n;

n->isconstraint =TRUE;

processCASbits($10, @10,"TRIGGER",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

n->constrrel =$9;

$$ = (Node *)n;

}

|INITIALLYIMMEDIATE{$$ = CAS_INITIALLY_IMMEDIATE; }

|INITIALLYDEFERRED{$$ = CAS_INITIALLY_DEFERRED; }

|NOTVALID{$$ = CAS_NOT_VALID; }

|NOINHERIT{$$ = CAS_NO_INHERIT; }

;

n->isconstraint =TRUE;

processCASbits($8, @8,"ASSERTION",

&n->deferrable, &n->initdeferred,NULL,

yyscanner);

NULL,yyscanner);

ereport(ERROR,

(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

static void

processCASbits(int cas_bits, int location, const char *constrType,

bool *deferrable, bool *initdeferred, bool *not_valid,

core_yyscan_t yyscanner)

bool *no_inherit,core_yyscan_t yyscanner)

{

if (deferrable)

constrType),

parser_errposition(location)));

}

if (cas_bits & CAS_NO_INHERIT)

{

if (no_inherit)

*no_inherit = true;

else

ereport(ERROR,

(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

errmsg("%s constraints cannot be marked NO INHERIT",

constrType),

parser_errposition(location)));

}

}

appendStringInfo(&buf,"CHECK %s(%s)",

conForm->connoinherit ?"NO INHERIT " :"",

consrc);

appendStringInfo(&buf,"CHECK (%s)%s",

consrc,

conForm->connoinherit ?" NO INHERIT" :"");

break;

}

caseCONSTRAINT_TRIGGER:

"con1foo" CHECK (a > 0)

Inherits: constraint_rename_test

ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECKNO INHERIT(b > 0);

ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;

ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok

ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok

\d constraint_rename_test

c | integer |

Check constraints:

"con1foo" CHECK (a > 0)

"con2bar" CHECKNO INHERIT(b > 0)

"con2bar" CHECK (b > 0) NO INHERIT

Number of child tables: 1 (Use \d+ to list them.)

\d constraint_rename_test2

"con3foo" PRIMARY KEY, btree (a)

Check constraints:

"con1foo" CHECK (a > 0)

"con2bar" CHECKNO INHERIT(b > 0)

"con2bar" CHECK (b > 0) NO INHERIT

Number of child tables: 1 (Use \d+ to list them.)

\d constraint_rename_test2

create table atacc1 (test int);

create table atacc2 (test2 int) inherits (atacc1);

-- ok:

alter table atacc1 add constraint foo checkno inherit(test>0);

alter table atacc1 add constraint foo check (test>0) no inherit;

-- check constraint is not there on child

insert into atacc2 (test) values (-3);

-- check constraint is there on parent

DETAIL: Failing row contains (-3).

insert into atacc1 (test) values (3);

-- fail, violating row:

alter table atacc2 add constraint foo checkno inherit(test>0);

alter table atacc2 add constraint foo check (test>0) no inherit;

ERROR: check constraint "foo" is violated by some row

drop table atacc2;

drop table atacc1;

-- Test non-inheritable parent constraints

create table p1(ff1 int);

alter table p1 add constraint p1chk checkno inherit(ff1 > 0);

alter table p1 add constraint p1chk check (ff1 > 0) no inherit;

alter table p1 add constraint p2chk check (ff1 > 10);

-- connoinherit should be true for NO INHERIT constraint

select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2;

--------+---------+-----------

ff1 | integer |

Check constraints:

"p1chk" CHECKNO INHERIT(ff1 > 0)

"p1chk" CHECK (ff1 > 0) NO INHERIT

"p2chk" CHECK (ff1 > 10)

Number of child tables: 1 (Use \d+ to list them.)

--

CREATE TABLE ATACC1 (TEST INT

CHECKNO INHERIT(TEST > 0));

CHECK (TEST > 0) NO INHERIT);

CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);

-- check constraint is not there on child

DROP TABLE ATACC1 CASCADE;

CREATE TABLE ATACC1 (TEST INT, TEST2 INT

CHECK (TEST > 0), CHECKNO INHERIT(TEST2 > 10));

CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT);

CREATE TABLE ATACC2 () INHERITS (ATACC1);

-- check constraint is there on child

-- Check NO INHERIT type of constraints and inheritance

--

CREATE TABLE ATACC1 (TEST INT

CHECKNO INHERIT(TEST > 0));

CHECK (TEST > 0) NO INHERIT);

CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);

-- check constraint is not there on child

INSERT INTO ATACC2 (TEST) VALUES (-3);

DROP TABLE ATACC1 CASCADE;

NOTICE: drop cascades to table atacc2

CREATE TABLE ATACC1 (TEST INT, TEST2 INT

CHECK (TEST > 0), CHECKNO INHERIT(TEST2 > 10));

CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT);

CREATE TABLE ATACC2 () INHERITS (ATACC1);

-- check constraint is there on child

INSERT INTO ATACC2 (TEST) VALUES (-3);