diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 13 | ||||
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 55 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 13 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 8 |
4 files changed, 29 insertions, 60 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b530c030f01..56deb4ac5fd 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1270,8 +1270,7 @@ <structfield>attnotnull</structfield> <type>bool</type> </para> <para> - This column is marked not-null, either by a not-null constraint - or a primary key. + This represents a not-null constraint. </para></entry> </row> @@ -2502,10 +2501,14 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </indexterm> <para> - The catalog <structname>pg_constraint</structname> stores check, not-null, - primary key, unique, foreign key, and exclusion constraints on tables. + The catalog <structname>pg_constraint</structname> stores check, primary + key, unique, foreign key, and exclusion constraints on tables, as well as + not-null constraints on domains. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) + Not-null constraints on relations are represented in the + <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link> + catalog, not here. </para> <para> @@ -2567,7 +2570,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <para> <literal>c</literal> = check constraint, <literal>f</literal> = foreign key constraint, - <literal>n</literal> = not-null constraint, + <literal>f</literal> = not-null constraint (domains only), <literal>p</literal> = primary key constraint, <literal>u</literal> = unique constraint, <literal>t</literal> = constraint trigger, diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 026bfff70f3..6aab79e901c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -763,38 +763,17 @@ CREATE TABLE products ( price numeric ); </programlisting> - An explicit constraint name can also be specified, for example: -<programlisting> -CREATE TABLE products ( - product_no integer NOT NULL, - name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL, - price numeric -); -</programlisting> - </para> - - <para> - A not-null constraint is usually written as a column constraint. The - syntax for writing it as a table constraint is -<programlisting> -CREATE TABLE products ( - product_no integer, - name text, - price numeric, - <emphasis>NOT NULL product_no</emphasis>, - <emphasis>NOT NULL name</emphasis> -); -</programlisting> - But this syntax is not standard and mainly intended for use by - <application>pg_dump</application>. </para> <para> - A not-null constraint is functionally equivalent to creating a check + A not-null constraint is always written as a column constraint. A + not-null constraint is functionally equivalent to creating a check constraint <literal>CHECK (<replaceable>column_name</replaceable> IS NOT NULL)</literal>, but in <productname>PostgreSQL</productname> creating an explicit - not-null constraint is more efficient. + not-null constraint is more efficient. The drawback is that you + cannot give explicit names to not-null constraints created this + way. </para> <para> @@ -812,10 +791,6 @@ CREATE TABLE products ( </para> <para> - However, a column can have at most one explicit not-null constraint. - </para> - - <para> The <literal>NOT NULL</literal> constraint has an inverse: the <literal>NULL</literal> constraint. This does not mean that the column must be null, which would surely be useless. Instead, this @@ -1008,7 +983,7 @@ CREATE TABLE example ( <para> A table can have at most one primary key. (There can be any number - of unique constraints, which combined with not-null constraints are functionally almost the + of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is @@ -1668,16 +1643,11 @@ ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; </programlisting> - </para> - - <para> - To add a not-null constraint, which is normally not written as a table - constraint, this special syntax is available: + To add a not-null constraint, which cannot be written as a table + constraint, use this syntax: <programlisting> ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; </programlisting> - This command silently does nothing if the column already has a - not-null constraint. </para> <para> @@ -1718,15 +1688,12 @@ ALTER TABLE products DROP CONSTRAINT some_name; </para> <para> - Simplified syntax is available to drop a not-null constraint: + This works the same for all constraint types except not-null + constraints. To drop a not-null constraint use: <programlisting> ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; </programlisting> - This mirrors the <literal>SET NOT NULL</literal> syntax for adding a - not-null constraint. This command will silently do nothing if the column - does not have a not-null constraint. (Recall that a column can have at - most one not-null constraint, so it is never ambiguous which constraint - this command acts on.) + (Recall that not-null constraints do not have names.) </para> </sect2> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 0bf11f6cb6d..5d352abf991 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase>and <replaceable class="parameter">column_constraint</replaceable> is:</phrase> [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] -{ NOT NULL [ NO INHERIT ] | +{ NOT NULL | NULL | CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | DEFAULT <replaceable>default_expr</replaceable> | @@ -121,7 +121,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | - NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <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> ) ] | @@ -1942,17 +1941,11 @@ ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central) <title>Compatibility</title> <para> - The forms <literal>ADD [COLUMN]</literal>, + The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>), <literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>, <literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>), <literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal> - conform with the SQL standard. - The form <literal>ADD <replaceable>table_constraint</replaceable></literal> - conforms with the SQL standard when the <literal>USING INDEX</literal> and - <literal>NOT VALID</literal> clauses are omitted and the constraint type is - one of <literal>CHECK</literal>, <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, - or <literal>REFERENCES</literal>. - The other forms are + conform with the SQL standard. The other forms are <productname>PostgreSQL</productname> extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single <command>ALTER TABLE</command> command is an extension. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 75f06bc49cc..a5bf80fb27e 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,7 +77,6 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | - NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <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> ) ] | @@ -2392,6 +2391,13 @@ CREATE TABLE cities_partdef constraint, and index names must be unique across all relations within the same schema. </para> + + <para> + Currently, <productname>PostgreSQL</productname> does not record names + for not-null constraints at all, so they are not + subject to the uniqueness restriction. This might change in a future + release. + </para> </refsect2> <refsect2> |