diff options
Diffstat (limited to 'doc/src/sgml/ref/create_table.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 791 |
1 files changed, 435 insertions, 356 deletions
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index b0525ff6ed3..998c370a3b8 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -12,7 +12,7 @@ <REFPURPOSE> Creates a new table </REFPURPOSE> - + </refnamediv> <REFSYNOPSISDIV> <REFSYNOPSISDIVINFO> <DATE>1998-09-11</DATE> @@ -20,8 +20,7 @@ <SYNOPSIS> CREATE TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE> - [ DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>] - [, NOT NULL ] [ ,UNIQUE ] + [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> ] [<REPLACEABLE>column_constraint_clause</REPLACEABLE> | PRIMARY KEY } [ ... ] ] [, ... ] [, PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) ] @@ -126,33 +125,32 @@ requires the <REPLACEABLE CLASS="PARAMETER">column_constraint_clause</REPLACEABL </LISTITEM> </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - INHERITS <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE> - </TERM> - <LISTITEM> - <PARA> - The optional INHERITS clause specifies a collection of table - names from which this table automatically inherits all fields. - If any inherited field name appears more than once, -<productname>Postgres</productname> - reports an error. - <productname>Postgres</productname> automatically allows the created - table to inherit functions on tables above it in the inheritance - hierarchy. -<note> -<title>Aside</title> -<para> - Inheritance of functions is done according - to the conventions of the Common Lisp Object System (CLOS). -</note> - </PARA> - </LISTITEM> - </VARLISTENTRY> - - </VARIABLELIST> - - + <VARLISTENTRY> + <TERM> + INHERITS <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE> + </TERM> + <LISTITEM> + <PARA> + The optional INHERITS clause specifies a collection of table + names from which this table automatically inherits all fields. + If any inherited field name appears more than once, + <productname>Postgres</productname> + reports an error. + <productname>Postgres</productname> automatically allows the created + table to inherit functions on tables above it in the inheritance + hierarchy. + <note> + <title>Aside</title> + <para> + Inheritance of functions is done according + to the conventions of the Common Lisp Object System (CLOS). + </para> + </note> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </para> </REFSECT2> <REFSECT2 ID="R2-SQL-CREATETABLE-2"> @@ -201,8 +199,11 @@ amcreate: "<replaceable class="parameter">table</replaceable>" relation already <PARA> if data type of default value doesn't match the column definition's data type. - - </VARIABLELIST> + </para> + </listitem> + </varlistentry> + </VARIABLELIST> + </para> </REFSECT2> </REFSYNOPSISDIV> @@ -217,7 +218,7 @@ amcreate: "<replaceable class="parameter">table</replaceable>" relation already <command>CREATE TABLE</command> will enter a new table into the current data base. The table will be "owned" by the user issuing the command. - + </para> <PARA> The new table is created as a heap with no initial data. A table can have no more than 1600 columns (realistically, @@ -226,9 +227,9 @@ amcreate: "<replaceable class="parameter">table</replaceable>" relation already lower at some sites. A table cannot have the same name as a system catalog table. </PARA> - + </refsect1> - <REFSECT1 ID="R1-SQL-DEFAULTCLAUSE-1"> + <REFSECT1 ID="R1-SQL-DEFAULTCLAUSE-1"> <REFSECT1INFO> <DATE>1998-09-11</DATE> </REFSECT1INFO> @@ -239,7 +240,7 @@ amcreate: "<replaceable class="parameter">table</replaceable>" relation already <SYNOPSIS> DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> </SYNOPSIS> - + </para> <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-1"> <REFSECT2INFO> <DATE>1998-09-11</DATE> @@ -270,23 +271,27 @@ DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> <listitem> <simpara> a niladic function - </simpara> - </listitem> - </itemizedlist> - </para> + </simpara> </listitem> - </VARLISTENTRY> - - </variablelist> + </itemizedlist> + </para> + </listitem> + </VARLISTENTRY> + </variablelist> + </para> + </refsect2> + <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-2"> <REFSECT2INFO> <DATE>1998-09-11</DATE> </REFSECT2INFO> <TITLE> - Outputs - </TITLE> - <PARA> - + Outputs + </TITLE> + <para> + </para> + </refsect2> + <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-3"> <REFSECT2INFO> <DATE>1998-09-11</DATE> @@ -365,6 +370,7 @@ DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> </listitem> </varlistentry> </variablelist> + </para> <para> In the current release (v6.4), <productname>Postgres</productname> @@ -386,7 +392,8 @@ DEFAULT CURRENT_TIMESTAMP </quote>. This forces <productname>Postgres</productname> to consider the constant a string type and then to convert the value to <type>timestamp</type> at runtime. - + </para> + </refsect2> <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-4"> <REFSECT2INFO> <DATE>1998-09-11</DATE> @@ -406,7 +413,7 @@ CREATE TABLE video_sales ( total CASH DEFAULT '$0.0' ); </ProgramListing> - + </para> <PARA> To assign an existing sequence as the default for the column <literal>did</literal>, @@ -418,7 +425,8 @@ CREATE TABLE distributors ( name VARCHAR(40) DEFAULT 'luso films' ); </ProgramListing> - + </para> + </refsect2> </REFSECT1> <REFSECT1 ID="R1-SQL-COLUMNCONSTRAINT-1"> @@ -430,8 +438,9 @@ CREATE TABLE distributors ( </TITLE> <para> <SYNOPSIS> -[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] { NOT NULL | UNIQUE | PRIMARY KEY | CHECK <replaceable class="parameter">constraint</replaceable> } [, ...] +[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] { [ NULL | NOT NULL ] | UNIQUE | PRIMARY KEY | CHECK <replaceable class="parameter">constraint</replaceable> } [, ...] </SYNOPSIS> + </para> <REFSECT2 ID="R2-SQL-COLUMNCONSTRAINT-1"> <REFSECT2INFO> @@ -459,6 +468,17 @@ which should ensure uniqueness for <VARLISTENTRY> <TERM> + NULL + </TERM> + <LISTITEM> + <PARA> +The column is allowed to contain NULL values. This is the default. + </PARA> + </LISTITEM> + </VARLISTENTRY> + + <VARLISTENTRY> + <TERM> NOT NULL </TERM> <LISTITEM> @@ -507,6 +527,8 @@ as a unique identifier for rows. </LISTITEM> </VARLISTENTRY> </VARIABLELIST> + </para> + </refsect2> <REFSECT2 ID="R2-SQL-COLUMNCONSTRAINT-2"> <REFSECT2INFO> @@ -544,6 +566,7 @@ as a unique identifier for rows. accepts the REFERENCES syntax but ignores the clause. </para> </note> + </refsect2> <REFSECT2 ID="R2-SQL-NOTNULL-1"> <REFSECT2INFO> @@ -564,69 +587,76 @@ accepts the REFERENCES syntax but ignores the clause. as a table constraint. </PARA> - <REFSECT3 ID="R3-SQL-NOTNULL-1"> - <REFSECT3INFO> - <DATE>1998-09-11</DATE> - </REFSECT3INFO> - <TITLE> - Outputs - </TITLE> - <PARA> - </PARA> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> -<replaceable>status</replaceable> - </TERM> - <LISTITEM> - <PARA> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> - <ReturnValue>ERROR: ExecAppend: Fail to add null value in not - null attribute "<replaceable class="parameter">column</replaceable>".</ReturnValue> - </TERM> - <LISTITEM> - <PARA> - This error occurs at runtime if one tries to insert a null value - into a column which has a NOT NULL constraint. - </PARA> - </LISTITEM> - </VARLISTENTRY> - </variablelist> - </LISTITEM> - </VARLISTENTRY> - </VARIABLELIST> - - <REFSECT3 ID="R3-SQL-NOTNULL-2"> - <REFSECT3INFO> - <DATE>1998-09-11</DATE> - </REFSECT3INFO> - <TITLE> -Description -</title> -<para> - - <REFSECT3 ID="R3-SQL-NOTNULL-3"> - <REFSECT3INFO> - <DATE>1998-09-11</DATE> - </REFSECT3INFO> - <TITLE> -Usage -</title> - - <PARA> - Define two NOT NULL column constraints on the table - <classname>distributors</classname>, -one of which being a named constraint: - </PARA> - <ProgramListing> -CREATE TABLE distributors ( - did DECIMAL(3) CONSTRAINT no_null NOT NULL, - name VARCHAR(40) NOT NULL -); - </ProgramListing> + <REFSECT3 ID="R3-SQL-NOTNULL-1"> + <REFSECT3INFO> + <DATE>1998-09-11</DATE> + </REFSECT3INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <replaceable>status</replaceable> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>ERROR: ExecAppend: Fail to add null value in not + null attribute "<replaceable class="parameter">column</replaceable>".</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This error occurs at runtime if one tries to insert a null value + into a column which has a NOT NULL constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </para> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </refsect3> + <REFSECT3 ID="R3-SQL-NOTNULL-2"> + <REFSECT3INFO> + <DATE>1998-09-11</DATE> + </REFSECT3INFO> + <TITLE> + Description + </title> + <para> + </para> + </refsect3> + + <REFSECT3 ID="R3-SQL-NOTNULL-3"> + <REFSECT3INFO> + <DATE>1998-09-11</DATE> + </REFSECT3INFO> + <TITLE> + Usage + </title> + + <PARA> + Define two NOT NULL column constraints on the table + <classname>distributors</classname>, + one of which being a named constraint: + + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(3) CONSTRAINT no_null NOT NULL, + name VARCHAR(40) NOT NULL + ); + </ProgramListing> + </para> + </refsect3> + </refsect2> + <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-1"> <REFSECT2INFO> <DATE>1998-09-11</DATE> @@ -640,21 +670,22 @@ CREATE TABLE distributors ( <refsect3> <title>Inputs</title> -<para> - <variablelist> - <varlistentry> - <term> - CONSTRAINT <replaceable class="parameter">name</replaceable> - </term> - <listitem> - <para> - An arbitrary label given to a constraint. - </para> - </listitem> - </varlistentry> - </variablelist> + <para> + <variablelist> + <varlistentry> + <term> + CONSTRAINT <replaceable class="parameter">name</replaceable> + </term> + <listitem> + <para> + An arbitrary label given to a constraint. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> </refsect3> - + <refsect3> <title>Outputs</title> <PARA> @@ -674,15 +705,17 @@ CREATE TABLE distributors ( <para> This error occurs at runtime if one tries to insert a duplicate value into a column. - </para> - </listitem> - </varlistentry> - </variablelist></para> - </listitem> - </varlistentry> - </variablelist> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> </refsect3> - + <refsect3> <title> Description @@ -713,6 +746,7 @@ for more details.). data integrity. See CREATE INDEX for more information. </Para> </Note> + </refsect3> <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-3"> <TITLE> @@ -737,6 +771,9 @@ CREATE TABLE distributors ( UNIQUE(name) ); </ProgramListing> + </para> + </refsect3> + </refsect2> <REFSECT2 ID="R2-SQL-CHECK-1"> <REFSECT2INFO> @@ -759,70 +796,73 @@ The CHECK Constraint <LISTITEM> <PARA> An arbitrary name given to a constraint. - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <replaceable>condition</replaceable> - </TERM> - <LISTITEM> - <PARA> - Any valid conditional expression evaluating to a boolean result. - </PARA> - </LISTITEM> - </VARLISTENTRY> - </variablelist> - </REFSECT3> - - <REFSECT3 ID="R3-SQL-CHECK-2"> - <REFSECT3INFO> - <DATE>1998-09-11</DATE> - </REFSECT3INFO> - <TITLE> - Outputs - </TITLE> - <PARA> - <VARIABLELIST> + </PARA> + </LISTITEM> + </VARLISTENTRY> <VARLISTENTRY> <TERM> -<replaceable>status</replaceable> + <replaceable>condition</replaceable> </TERM> <LISTITEM> <PARA> - + Any valid conditional expression evaluating to a boolean result. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </para> + </REFSECT3> + + <REFSECT3 ID="R3-SQL-CHECK-2"> + <REFSECT3INFO> + <DATE>1998-09-11</DATE> + </REFSECT3INFO> + <TITLE> + Outputs + </TITLE> + <PARA> <VARIABLELIST> <VARLISTENTRY> <TERM> - <ReturnValue> - ERROR: ExecAppend: rejected due to CHECK constraint - "<replaceable class="parameter">table_column</replaceable>". - </ReturnValue> + <replaceable>status</replaceable> </TERM> <LISTITEM> <PARA> - This error occurs at runtime if one tries to insert an illegal - value into a column subject to a CHECK constraint. - </PARA> - </LISTITEM> - </VARLISTENTRY> - </variablelist> + + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue> + ERROR: ExecAppend: rejected due to CHECK constraint + "<replaceable class="parameter">table_column</replaceable>". + </ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This error occurs at runtime if one tries to insert an illegal + value into a column subject to a CHECK constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </para> </LISTITEM> </VARLISTENTRY> </variablelist> + </para> </REFSECT3> - + <refsect3> <title>Description</title> <para> The CHECK constraint specifies a restriction on allowed values -within a column. + within a column. The CHECK constraint is also allowed as a table constraint. </PARA> <PARA> The SQL92 CHECK column constraints can only be defined on, and refer to, one column of the table. <productname>Postgres</productname> - does not have + does not have this restriction. </PARA> </refsect3> @@ -868,7 +908,7 @@ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> <para> This occurs at run-time if one tries to insert a duplicate value into a column subject to a PRIMARY KEY constraint. - </PARA> + </PARA> </listitem> </varlistentry> </variablelist> @@ -909,83 +949,89 @@ However, <productname>Postgres</productname> does not specifically disallow this. </PARA> </refsect3> - - <REFSECT1 ID="R1-SQL-TABLECONSTRAINT-1"> - <REFSECT1INFO> - <DATE>1998-09-11</DATE> - </REFSECT1INFO> - <TITLE> + </refsect2> + </refsect1> + + <REFSECT1 ID="R1-SQL-TABLECONSTRAINT-1"> + <REFSECT1INFO> + <DATE>1998-09-11</DATE> + </REFSECT1INFO> + <TITLE> Table CONSTRAINT Clause - </TITLE> - <para> + </TITLE> + <para> <SYNOPSIS> -[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( <replaceable class="parameter">column</replaceable> [, ...] ) -[ CONSTRAINT name ] CHECK ( <replaceable>constraint</replaceable> ) + [ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( <replaceable class="parameter">column</replaceable> [, ...] ) + [ CONSTRAINT name ] CHECK ( <replaceable>constraint</replaceable> ) </SYNOPSIS> - <PARA> - + </para> <REFSECT2 ID="R2-SQL-TABLECONSTRAINT-1"> <REFSECT2INFO> <DATE>1998-09-11</DATE> </REFSECT2INFO> -<title> -Inputs -</title> - -<para> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> - CONSTRAINT <replaceable class="parameter">name</replaceable> - </TERM> - <LISTITEM> - <PARA> - An arbitrary name given to an integrity constraint. - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <replaceable class="parameter">column</replaceable> [, ...] - </TERM> - <LISTITEM> - <PARA> - The column name(s) for which to define a unique index -and, for PRIMARY KEY, a NOT NULL constraint. - </PARA> - </LISTITEM> - <VARLISTENTRY> - <TERM> - CHECK ( <replaceable class="parameter">constraint</replaceable> ) - </TERM> - <LISTITEM> - <PARA> - A boolean expression to be evaluated as the constraint. - </PARA> - </LISTITEM> - </VARLISTENTRY> - </VARIABLELIST> - + <title> + Inputs + </title> + + <para> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + CONSTRAINT <replaceable class="parameter">name</replaceable> + </TERM> + <LISTITEM> + <PARA> + An arbitrary name given to an integrity constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">column</replaceable> [, ...] + </TERM> + <LISTITEM> + <PARA> + The column name(s) for which to define a unique index + and, for PRIMARY KEY, a NOT NULL constraint. + </PARA> + </LISTITEM> + </varlistentry> + <VARLISTENTRY> + <TERM> + CHECK ( <replaceable class="parameter">constraint</replaceable> ) + </TERM> + <LISTITEM> + <PARA> + A boolean expression to be evaluated as the constraint. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </para> + </refsect2> + <REFSECT2 ID="R2-SQL-TABLECONSTRAINT-2"> <REFSECT2INFO> <DATE>1998-09-11</DATE> </REFSECT2INFO> -<title> -Outputs -</title> - -<para> -The possible outputs for the table constraint clause are the same -as for the corresponding portions of the column constraint clause. - + <title> + Outputs + </title> + + <para> + The possible outputs for the table constraint clause are the same + as for the corresponding portions of the column constraint clause. + </para> + </refsect2> + <REFSECT2 ID="R2-SQL-TABLECONSTRAINT-3"> <REFSECT2INFO> <DATE>1998-09-11</DATE> </REFSECT2INFO> -<title> -Description -</title> - + <title> + Description + </title> + <para> A table constraint is an integrity constraint defined on one or more columns of a base table. The four variations of "Table @@ -1000,14 +1046,15 @@ Description <note> <para> <productname>Postgres</productname> does not yet -(as of version 6.4) support FOREIGN KEY -integrity constraints. The parser understands the FOREIGN KEY syntax, -but only prints a notice and otherwise ignores the clause. + (as of version 6.4) support FOREIGN KEY + integrity constraints. The parser understands the FOREIGN KEY syntax, + but only prints a notice and otherwise ignores the clause. Foreign keys may be partially emulated by triggers (See the CREATE TRIGGER statement). </para> </note> - + </refsect2> + <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-4"> <REFSECT2INFO> <DATE>1998-09-11</DATE> @@ -1016,15 +1063,16 @@ but only prints a notice and otherwise ignores the clause. UNIQUE Constraint </TITLE> <para> - <synopsis> -[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] ) - </SYNOPSIS> + <synopsis> + [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] ) + </SYNOPSIS> + </para> <refsect3> <title>Inputs</title> <variablelist> <varlistentry> <term> - CONSTRAINT <replaceable class="parameter">name</replaceable> + CONSTRAINT <replaceable class="parameter">name</replaceable> </term> <listitem> <para> @@ -1044,64 +1092,69 @@ but only prints a notice and otherwise ignores the clause. </varlistentry> </variablelist> </refsect3> + <refsect3> <title>Outputs</title> <PARA> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> -<replaceable>status</replaceable> - </TERM> - <LISTITEM> - <PARA> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> - ERROR: Cannot insert a duplicate key into a unique index. - </term> - <listitem> - <para> - This error occurs at runtime if one tries to insert a - duplicate value into a column. - </para> - </listitem> - </varlistentry> - </variablelist> - </listitem> - </varlistentry> - </variablelist> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <replaceable>status</replaceable> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + ERROR: Cannot insert a duplicate key into a unique index. + </term> + <listitem> + <para> + This error occurs at runtime if one tries to insert a + duplicate value into a column. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> </refsect3> - + <refsect3> <title> -Description + Description </title> - + <PARA> The UNIQUE constraint specifies a rule that a group of one or more distinct columns of a table may contain only unique values. -The behavior of the UNIQUE table constraint is the same as that for column -constraints, with the additional capability to span multiple columns. + The behavior of the UNIQUE table constraint is the same as that for column + constraints, with the additional capability to span multiple columns. </para> <para> -See the section on the UNIQUE column constraint for more details. - - <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-4"> - <TITLE> -Usage -</title> - - <PARA> - Define a UNIQUE table constraint for the table distributors: - <ProgramListing> -CREATE TABLE distributors ( - did DECIMAL(03), - name VARCHAR(40), - UNIQUE(name) -); - </ProgramListing> - + See the section on the UNIQUE column constraint for more details. + </para> + </refsect3> + <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-4"> + <TITLE> + Usage + </title> + + <PARA> + Define a UNIQUE table constraint for the table distributors: + <ProgramListing> + CREATE TABLE distributors ( + did DECIMAL(03), + name VARCHAR(40), + UNIQUE(name) + ); + </ProgramListing> + </para> + </refsect3> </REFSECT2> <REFSECT2 ID="R2-SQL-PRIMARYKEY-4"> @@ -1111,18 +1164,18 @@ CREATE TABLE distributors ( <TITLE> PRIMARY KEY Constraint </TITLE> -<para> - <SYNOPSIS> - [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) - </SYNOPSIS> - + <para> + <SYNOPSIS> + [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) + </SYNOPSIS> + </para> <refsect3> <title>Inputs</title> <PARA> <VARIABLELIST> <VARLISTENTRY> <TERM> -CONSTRAINT <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></ReturnValue> + CONSTRAINT <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></ReturnValue> </TERM> <LISTITEM> <PARA> @@ -1132,7 +1185,7 @@ CONSTRAINT <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></Retur </VARLISTENTRY> <VARLISTENTRY> <TERM> -<REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] + <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] </TERM> <LISTITEM> <PARA> @@ -1143,32 +1196,33 @@ CONSTRAINT <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></Retur </VARIABLELIST> </para> </refsect3> - + <refsect3> <title>Outputs</title> <variablelist> <varlistentry> <term> -<replaceable>status</replaceable> -</term> -<listitem> -<para> - <variablelist> - <varlistentry> - <term>ERROR: Cannot insert a duplicate key into a unique index.</term> + <replaceable>status</replaceable> + </term> <listitem> <para> - This occurs at run-time if one tries to insert a duplicate value into - a column subject to a PRIMARY KEY constraint. - </PARA> - </listitem> - </varlistentry> - </variablelist> + <variablelist> + <varlistentry> + <term>ERROR: Cannot insert a duplicate key into a unique index.</term> + <listitem> + <para> + This occurs at run-time if one tries to insert a duplicate value into + a column subject to a PRIMARY KEY constraint. + </PARA> + </listitem> + </varlistentry> + </variablelist> + </para> </listitem> </varlistentry> </variablelist> </refsect3> - + <refsect3> <title>Description</title> <PARA> @@ -1177,17 +1231,18 @@ CONSTRAINT <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></Retur (non duplicate), non-null values. The column definitions of the specified columns do not have to include a NOT NULL constraint to be included in a PRIMARY KEY constraint. - -The PRIMARY KEY table constraint is similar to that for column constraints, -with the additional capability of encompassing multiple columns. + + The PRIMARY KEY table constraint is similar to that for column constraints, + with the additional capability of encompassing multiple columns. </PARA> <PARA> -Refer to the section on the PRIMARY KEY column constraint for more -information. + Refer to the section on the PRIMARY KEY column constraint for more + information. + </para> </REFSECT3> </REFSECT2> - + </refsect1> <REFSECT1 ID="R1-SQL-CREATETABLE-2"> @@ -1306,7 +1361,7 @@ information. </TITLE> <PARA> CREATE TABLE/INHERITS is a <productname>Postgres</productname> - language extension. + language extension. </PARA> </refsect2> @@ -1316,7 +1371,6 @@ information. <TITLE> Compatibility </TITLE> - <PARA> <REFSECT2 ID="R2-SQL-CREATETABLE-4"> <REFSECT2INFO> @@ -1357,15 +1411,17 @@ information. ) ON COMMIT DELETE ROWS </programlisting> <para> -Temporary tables are not currently available - in <productname>Postgres</productname>. -<tip> - <para> - In the current release of <productname>Postgres</productname> - (v6.4), to create a temporary - table you must create and drop the table by explicit commands. -</tip> - + Temporary tables are not currently available + in <productname>Postgres</productname>. + <tip> + <para> + In the current release of <productname>Postgres</productname> + (v6.4), to create a temporary + table you must create and drop the table by explicit commands. + </para> + </tip> + </para> + <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-1"> <REFSECT3INFO> <DATE>1998-09-11</DATE> @@ -1375,6 +1431,7 @@ Temporary tables are not currently available </TITLE> <PARA> SQL92 specifies some additional capabilities for UNIQUE: + </para> <para> Table Constraint definition </PARA> @@ -1395,6 +1452,23 @@ Temporary tables are not currently available </synopsis> </refsect3> + <REFSECT3 ID="R3-SQL-NULL-1"> + <REFSECT3INFO> + <DATE>1998-12-24</DATE> + </REFSECT3INFO> + <TITLE> + NULL clause + </TITLE> + <PARA> + The NULL "constraint" (actually a non-constraint) + is a <productname>Postgres</productname> extension to SQL92 + is included for symmetry with the NOT NULL clause. Since it is the default + for any column, its presence is simply noise. + <synopsis> + [ CONSTRAINT name ] NULL + </synopsis> + </REFSECT3> + <REFSECT3 ID="R3-SQL-NOTNULL-4"> <REFSECT3INFO> <DATE>1998-09-11</DATE> @@ -1407,9 +1481,9 @@ Temporary tables are not currently available SQL92 specifies some additional capabilities for NOT NULL: </PARA> <synopsis> - [ CONSTRAINT name ] NOT NULL - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] + [ CONSTRAINT name ] NOT NULL + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] </synopsis> </REFSECT3> @@ -1450,13 +1524,13 @@ the column. Not our problem... <PARA> SQL92 specifies some additional capabilities for constraints, and also defines assertions and domain constraints. - <note> - <para> - <productname>Postgres</productname> does not yet support -either domains or assertions. + <note> + <para> + <productname>Postgres</productname> does not yet support + either domains or assertions. + </para> + </note> </para> - </note> - <PARA> An assertion is a special type of integrity constraint and share the same namespace as other constraints. @@ -1672,7 +1746,7 @@ affect a column or a table. <REFPURPOSE> Creates a new table </REFPURPOSE> - + </refnamediv> <REFSYNOPSISDIV> <REFSYNOPSISDIVINFO> <DATE>1998-09-22</DATE> @@ -1722,10 +1796,12 @@ a comma-delimited list of column names. <PARA> A valid query statement. Refer to SELECT for a description of the allowed syntax. - </PARA> - </LISTITEM> - </VARLISTENTRY> - </VARIABLELIST> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </para> + </refsect2> <REFSECT2 ID="R2-SQL-CREATETABLEAS-2"> <REFSECT2INFO> @@ -1735,22 +1811,25 @@ allowed syntax. Outputs </TITLE> <PARA> - Refer to CREATE TABLE and SELECT for a summary of possible output -messages. - - <REFSECT1 ID="R1-SQL-CREATETABLEAS-1"> - <REFSECT1INFO> - <DATE>1998-09-22</DATE> - </REFSECT1INFO> - <TITLE> - Description - </TITLE> + Refer to CREATE TABLE and SELECT for a summary of possible output + messages. + </para> + </refsect2> + </refsynopsisdiv> + + <REFSECT1 ID="R1-SQL-CREATETABLEAS-1"> + <REFSECT1INFO> + <DATE>1998-09-22</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> <PARA> CREATE TABLE AS enables a table to be created from the contents of an existing table. It has functionality equivalent to SELECT TABLE INTO, but with perhaps a more obvious syntax. - -</refsect1> + </para> + </refsect1> </refentry> <!-- Keep this comment at the end of the file |