From a75f2d21a8366aece67b8aa144a8644f6195e75f Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 29 Dec 1998 02:24:47 +0000 Subject: Clean up to ensure tag completion as required by the newest versions of Norm's Modular Style Sheets and jade/docbook. From Vince Vielhaber . --- doc/src/sgml/ref/create_table.sgml | 791 ++++++++++++++++++++----------------- 1 file changed, 435 insertions(+), 356 deletions(-) (limited to 'doc/src/sgml/ref/create_table.sgml') diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index b0525ff6ed..998c370a3b 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -12,7 +12,7 @@ Creates a new table - + 1998-09-11 @@ -20,8 +20,7 @@ CREATE TABLE table ( column type - [ DEFAULT value] - [, NOT NULL ] [ ,UNIQUE ] + [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] [, ... ] [, PRIMARY KEY ( column [, ...] ) ] @@ -126,33 +125,32 @@ requires the column_constraint_clause - - - INHERITS inherited_table - - - - 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, -Postgres - reports an error. - Postgres automatically allows the created - table to inherit functions on tables above it in the inheritance - hierarchy. - -Aside - - Inheritance of functions is done according - to the conventions of the Common Lisp Object System (CLOS). - - - - - - - - + + + INHERITS inherited_table + + + + 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, + Postgres + reports an error. + Postgres automatically allows the created + table to inherit functions on tables above it in the inheritance + hierarchy. + + Aside + + Inheritance of functions is done according + to the conventions of the Common Lisp Object System (CLOS). + + + + + + + @@ -201,8 +199,11 @@ amcreate: "table" relation already if data type of default value doesn't match the column definition's data type. - - + + + + + @@ -217,7 +218,7 @@ amcreate: "table" relation already CREATE TABLE will enter a new table into the current data base. The table will be "owned" by the user issuing the command. - + 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: "table" relation already lower at some sites. A table cannot have the same name as a system catalog table. - + - + 1998-09-11 @@ -239,7 +240,7 @@ amcreate: "table" relation already DEFAULT value - + 1998-09-11 @@ -270,23 +271,27 @@ DEFAULT value a niladic function - - - - + - - - + + + + + + + + 1998-09-11 - Outputs - - - + Outputs + + + + + 1998-09-11 @@ -365,6 +370,7 @@ DEFAULT value + In the current release (v6.4), Postgres @@ -386,7 +392,8 @@ DEFAULT CURRENT_TIMESTAMP . This forces Postgres to consider the constant a string type and then to convert the value to timestamp at runtime. - + + 1998-09-11 @@ -406,7 +413,7 @@ CREATE TABLE video_sales ( total CASH DEFAULT '$0.0' ); - + To assign an existing sequence as the default for the column did, @@ -418,7 +425,8 @@ CREATE TABLE distributors ( name VARCHAR(40) DEFAULT 'luso films' ); - + + @@ -430,8 +438,9 @@ CREATE TABLE distributors ( -[ CONSTRAINT name ] { NOT NULL | UNIQUE | PRIMARY KEY | CHECK constraint } [, ...] +[ CONSTRAINT name ] { [ NULL | NOT NULL ] | UNIQUE | PRIMARY KEY | CHECK constraint } [, ...] + @@ -457,6 +466,17 @@ which should ensure uniqueness for + + + NULL + + + +The column is allowed to contain NULL values. This is the default. + + + + NOT NULL @@ -507,6 +527,8 @@ as a unique identifier for rows. + + @@ -544,6 +566,7 @@ as a unique identifier for rows. accepts the REFERENCES syntax but ignores the clause. + @@ -564,69 +587,76 @@ accepts the REFERENCES syntax but ignores the clause. as a table constraint. - - - 1998-09-11 - - - Outputs - - - - - - -status - - - - - - - ERROR: ExecAppend: Fail to add null value in not - null attribute "column". - - - - This error occurs at runtime if one tries to insert a null value - into a column which has a NOT NULL constraint. - - - - - - - - - - - 1998-09-11 - - -Description - - - - - - 1998-09-11 - - -Usage - - - - Define two NOT NULL column constraints on the table - distributors, -one of which being a named constraint: - - -CREATE TABLE distributors ( - did DECIMAL(3) CONSTRAINT no_null NOT NULL, - name VARCHAR(40) NOT NULL -); - + + + 1998-09-11 + + + Outputs + + + + + + + status + + + + + + + ERROR: ExecAppend: Fail to add null value in not + null attribute "column". + + + + This error occurs at runtime if one tries to insert a null value + into a column which has a NOT NULL constraint. + + + + + + + + + + + + 1998-09-11 + + + Description + + + + + + + + 1998-09-11 + + + Usage + + + + Define two NOT NULL column constraints on the table + distributors, + one of which being a named constraint: + + + CREATE TABLE distributors ( + did DECIMAL(3) CONSTRAINT no_null NOT NULL, + name VARCHAR(40) NOT NULL + ); + + + + + 1998-09-11 @@ -640,21 +670,22 @@ CREATE TABLE distributors ( Inputs - - - - - CONSTRAINT name - - - - An arbitrary label given to a constraint. - - - - + + + + + CONSTRAINT name + + + + An arbitrary label given to a constraint. + + + + + - + Outputs @@ -674,15 +705,17 @@ CREATE TABLE distributors ( This error occurs at runtime if one tries to insert a duplicate value into a column. - - - - - - - + + + + + + + + + - + 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 - - - + + + -status + condition - + Any valid conditional expression evaluating to a boolean result. + + + + + + + + + + 1998-09-11 + + + Outputs + + - - ERROR: ExecAppend: rejected due to CHECK constraint - "table_column". - + status - This error occurs at runtime if one tries to insert an illegal - value into a column subject to a CHECK constraint. - - - - + + + + + + ERROR: ExecAppend: rejected due to CHECK constraint + "table_column". + + + + + This error occurs at runtime if one tries to insert an illegal + value into a column subject to a CHECK constraint. + + + + + + - + Description 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. The SQL92 CHECK column constraints can only be defined on, and refer to, one column of the table. Postgres - does not have + does not have this restriction. @@ -868,7 +908,7 @@ CONSTRAINT name This occurs at run-time if one tries to insert a duplicate value into a column subject to a PRIMARY KEY constraint. - + @@ -909,83 +949,89 @@ However, Postgres does not specifically disallow this. - - - - 1998-09-11 - - + </refsect2> + </refsect1> + + <REFSECT1 ID="R1-SQL-TABLECONSTRAINT-1"> + <REFSECT1INFO> + <DATE>1998-09-11</DATE> + </REFSECT1INFO> + <TITLE> Table CONSTRAINT Clause - - + + -[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( column [, ...] ) -[ CONSTRAINT name ] CHECK ( constraint ) + [ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( column [, ...] ) + [ CONSTRAINT name ] CHECK ( constraint ) - - + 1998-09-11 - -Inputs - - - - - - - CONSTRAINT name - - - - An arbitrary name given to an integrity constraint. - - - - - - column [, ...] - - - - The column name(s) for which to define a unique index -and, for PRIMARY KEY, a NOT NULL constraint. - - - - - CHECK ( constraint ) - - - - A boolean expression to be evaluated as the constraint. - - - - - + + Inputs + + + + + + + CONSTRAINT name + + + + An arbitrary name given to an integrity constraint. + + + + + + column [, ...] + + + + The column name(s) for which to define a unique index + and, for PRIMARY KEY, a NOT NULL constraint. + + + + + + CHECK ( constraint ) + + + + A boolean expression to be evaluated as the constraint. + + + + + + + 1998-09-11 - -Outputs - - - -The possible outputs for the table constraint clause are the same -as for the corresponding portions of the column constraint clause. - + + Outputs + + + + The possible outputs for the table constraint clause are the same + as for the corresponding portions of the column constraint clause. + + + 1998-09-11 - -Description - - + + Description + + 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 Postgres 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). - + + 1998-09-11 @@ -1016,15 +1063,16 @@ but only prints a notice and otherwise ignores the clause. UNIQUE Constraint - -[ CONSTRAINT name ] UNIQUE ( column [, ...] ) - + + [ CONSTRAINT name ] UNIQUE ( column [, ...] ) + + Inputs - CONSTRAINT name + CONSTRAINT name @@ -1044,64 +1092,69 @@ but only prints a notice and otherwise ignores the clause. + Outputs - - - -status - - - - - - - ERROR: Cannot insert a duplicate key into a unique index. - - - - This error occurs at runtime if one tries to insert a - duplicate value into a column. - - - - - - - + + + + status + + + + + + + ERROR: Cannot insert a duplicate key into a unique index. + + + + This error occurs at runtime if one tries to insert a + duplicate value into a column. + + + + + + + + + - + -Description + Description - + 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. -See the section on the UNIQUE column constraint for more details. - - - -Usage - - - - Define a UNIQUE table constraint for the table distributors: - -CREATE TABLE distributors ( - did DECIMAL(03), - name VARCHAR(40), - UNIQUE(name) -); - - + See the section on the UNIQUE column constraint for more details. + + + + + Usage + + + + Define a UNIQUE table constraint for the table distributors: + + CREATE TABLE distributors ( + did DECIMAL(03), + name VARCHAR(40), + UNIQUE(name) + ); + + + @@ -1111,18 +1164,18 @@ CREATE TABLE distributors ( PRIMARY KEY Constraint - - - [ CONSTRAINT name ] PRIMARY KEY ( column [, ...] ) - - + + + [ CONSTRAINT name ] PRIMARY KEY ( column [, ...] ) + + Inputs -CONSTRAINT name + CONSTRAINT name @@ -1132,7 +1185,7 @@ CONSTRAINT name -column [, ...] + column [, ...] @@ -1143,32 +1196,33 @@ CONSTRAINT name - + Outputs -status - - - - - - ERROR: Cannot insert a duplicate key into a unique index. + status + - This occurs at run-time if one tries to insert a duplicate value into - a column subject to a PRIMARY KEY constraint. - - - - + + + ERROR: Cannot insert a duplicate key into a unique index. + + + This occurs at run-time if one tries to insert a duplicate value into + a column subject to a PRIMARY KEY constraint. + + + + + - + Description @@ -1177,17 +1231,18 @@ CONSTRAINT name -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. + - + @@ -1306,7 +1361,7 @@ information. CREATE TABLE/INHERITS is a Postgres - language extension. + language extension. @@ -1316,7 +1371,6 @@ information. Compatibility - @@ -1357,15 +1411,17 @@ information. ) ON COMMIT DELETE ROWS -Temporary tables are not currently available - in Postgres. - - - In the current release of Postgres - (v6.4), to create a temporary - table you must create and drop the table by explicit commands. - - + Temporary tables are not currently available + in Postgres. + + + In the current release of Postgres + (v6.4), to create a temporary + table you must create and drop the table by explicit commands. + + + + 1998-09-11 @@ -1375,6 +1431,7 @@ Temporary tables are not currently available SQL92 specifies some additional capabilities for UNIQUE: + Table Constraint definition @@ -1395,6 +1452,23 @@ Temporary tables are not currently available + + + 1998-12-24 + + + NULL clause + + + The NULL "constraint" (actually a non-constraint) + is a Postgres 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. + + [ CONSTRAINT name ] NULL + + + 1998-09-11 @@ -1407,9 +1481,9 @@ Temporary tables are not currently available SQL92 specifies some additional capabilities for NOT NULL: - [ CONSTRAINT name ] NOT NULL - [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] - [ [ NOT ] DEFERRABLE ] + [ CONSTRAINT name ] NOT NULL + [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ] + [ [ NOT ] DEFERRABLE ] @@ -1450,13 +1524,13 @@ the column. Not our problem... SQL92 specifies some additional capabilities for constraints, and also defines assertions and domain constraints. - - - Postgres does not yet support -either domains or assertions. + + + Postgres does not yet support + either domains or assertions. + + - - 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. Creates a new table - + 1998-09-22 @@ -1722,10 +1796,12 @@ a comma-delimited list of column names. A valid query statement. Refer to SELECT for a description of the allowed syntax. - - - - + + + + + + @@ -1735,22 +1811,25 @@ allowed syntax. Outputs - Refer to CREATE TABLE and SELECT for a summary of possible output -messages. - - - - 1998-09-22 - - - Description - + Refer to CREATE TABLE and SELECT for a summary of possible output + messages. + + + + + + + 1998-09-22 + + + Description + 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. - - + +