Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite056c55

Browse files
committed
Catalog NOT NULL constraints
We now create pg_constaint rows for NOT NULL constraints withcontype='n'.We propagate these constraints during operations such as addinginheritance relationships, creating and attaching partitions, creatingtables LIKE other tables. We mostly follow the well-known rules ofconislocal and coninhcount that we have for CHECK constraints, with someadaptations; for example, as opposed to CHECK constraints, we don'tmatch NOT NULL ones by name when descending a hierarchy to alter it;instead we match by column number. This means we don't require theconstraint names to be identical across a hierarchy.For now, we omit them from system catalogs. Maybe this is worthreconsidering. We don't support NOT VALID nor DEFERRABLE clauseseither; these can be added as separate features later (this patch isalready large and complicated enough.)This has been very long in the making. The first patch was written byBernd Helmle in 2010 to add a new pg_constraint.contype value ('n'),which I (Álvaro) then hijacked in 2011 and 2012, until that one waskilled by the realization that we ought to use contype='c' instead:manufactured CHECK constraints. However, later SQL standarddevelopment, as well as nonobvious emergent properties of that design(mostly, failure to distinguish them from "normal" CHECK constraints aswell as the performance implication of having to test the CHECKexpression) led us to reconsider this choice, so now the currentimplementation uses contype='n' again.In 2016 Vitaly Burovoy also worked on this feature[1] but found noconsensus for his proposed approach, which was claimed to be closer tothe letter of the standard, requiring additional pg_attribute columns totrack the OID of the NOT NULL constraint for that column.[1]https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.comAuthor: Álvaro Herrera <alvherre@alvh.no-ip.org>Author: Bernd Helmle <mailings@oopsware.de>Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>Discussion:https://postgr.es/m/CACA0E642A0267EDA387AF2B%40%5B172.26.14.62%5DDiscussion:https://postgr.es/m/AANLkTinLXMOEMz+0J29tf1POokKi4XDkWJ6-DDR9BKgU@mail.gmail.comDiscussion:https://postgr.es/m/20110707213401.GA27098@alvh.no-ip.orgDiscussion:https://postgr.es/m/1343682669-sup-2532@alvh.no-ip.orgDiscussion:https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.comDiscussion:https://postgr.es/m/20220817181249.q7qvj3okywctra3c@alvherre.pgsql
1 parentff245a3 commite056c55

File tree

42 files changed

+2868
-625
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

42 files changed

+2868
-625
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2552,6 +2552,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
25522552
<para>
25532553
<literal>c</literal> = check constraint,
25542554
<literal>f</literal> = foreign key constraint,
2555+
<literal>n</literal> = not null constraint,
25552556
<literal>p</literal> = primary key constraint,
25562557
<literal>u</literal> = unique constraint,
25572558
<literal>t</literal> = constraint trigger,

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -117,7 +117,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
117117
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
118118
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> ) ] |
119119
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
120-
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
120+
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] |
121+
NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ]
122+
}
121123
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
122124

123125
<phrase>and <replaceable class="parameter">table_constraint_using_index</replaceable> is:</phrase>
@@ -1763,11 +1765,17 @@ ALTER TABLE measurement
17631765
<title>Compatibility</title>
17641766

17651767
<para>
1766-
The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
1768+
The forms <literal>ADD COLUMN</literal>
17671769
<literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>,
17681770
<literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>),
17691771
<literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal>
1770-
conform with the SQL standard. The other forms are
1772+
conform with the SQL standard.
1773+
The form <literal>ADD <replaceable>table_constraint</replaceable></literal>
1774+
conforms with the SQL standard when the <literal>USING INDEX</literal> and
1775+
<literal>NOT VALID</literal> clauses are omitted and the constraint type is
1776+
one of <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>
1777+
or <literal>REFERENCES</literal>.
1778+
The other forms are
17711779
<productname>PostgreSQL</productname> extensions of the SQL standard.
17721780
Also, the ability to specify more than one manipulation in a single
17731781
<command>ALTER TABLE</command> command is an extension.

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 1 addition & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
7777

7878
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
7979
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
80+
NOT NULL <replaceable class="parameter">column_name</replaceable> |
8081
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
8182
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
8283
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> ) ] |
@@ -2314,13 +2315,6 @@ CREATE TABLE cities_partdef
23142315
constraint, and index names must be unique across all relations within
23152316
the same schema.
23162317
</para>
2317-
2318-
<para>
2319-
Currently, <productname>PostgreSQL</productname> does not record names
2320-
for <literal>NOT NULL</literal> constraints at all, so they are not
2321-
subject to the uniqueness restriction. This might change in a future
2322-
release.
2323-
</para>
23242318
</refsect2>
23252319

23262320
<refsect2>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp