From f947bbb3b2ae270d0072aab85b538ba613e44054 Mon Sep 17 00:00:00 2001
From: Bruce Momjian
Date: Tue, 11 Apr 2000 14:43:54 +0000
Subject: Attached is are diffs for CREATE/ALTER table doc I've
forgotten in my mailbox (sorry). Haven't tried to apply and since I
don't have working sgml stuff cannot check.
Jan
---
doc/src/sgml/ref/alter_table.sgml | 43 ++-
doc/src/sgml/ref/create_table.sgml | 558 ++++++++++++++++++++++++++++++++++++-
doc/src/sgml/ref/pg_dump.sgml | 12 +-
3 files changed, 602 insertions(+), 11 deletions(-)
(limited to 'doc/src/sgml')
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index ca302cf7987..8305f75d28e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1,5 +1,5 @@
@@ -34,6 +34,8 @@ ALTER TABLE table [ * ]
class="PARAMETER">newcolumn
ALTER TABLE table
RENAME TO newtable
+ALTER TABLE table
+ ADD table constraint definition
@@ -89,6 +91,15 @@ ALTER TABLE table
+
+
+ table constraint definition
+
+
+ New table constraint for the table
+
+
+
@@ -144,6 +155,9 @@ ALTER TABLE table
the affected table. Thus, the table or column will
remain of the same type and size after this command is
executed.
+ The ADD table constraint definition clause
+ adds a new constraint to the table using the same syntax as .
@@ -188,6 +202,16 @@ SELECT NewColumn FROM SuperClass.)
+
+ In the current implementation, only FOREIGN KEY constraints can
+ be added to a table. To create or remove a unique constraint, create
+ a unique index (see ). To add check constraints
+ you need to recreate and reload the table, using other
+ parameters to the command.
+
+
You must own the class in order to change its schema.
Renaming any part of the schema of a system
@@ -227,6 +251,13 @@ ALTER TABLE distributors RENAME COLUMN address TO city;
ALTER TABLE distributors RENAME TO suppliers;
+
+
+ To add a foreign key constraint to a table:
+
+ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL
+
+
@@ -253,17 +284,15 @@ ALTER TABLE distributors RENAME TO suppliers;
-ALTER TABLE table ADD table constraint definition
ALTER TABLE table DROP CONSTRAINT constraint { RESTRICT | CASCADE }
- Adds or removes a table constraint (such as a check constraint,
- unique constraint, or foreign key constraint). To create
- or remove a unique constraint, create or drop a unique index,
- respectively (see ).
- To change other kinds of constraints you need to recreate
+ Removes a table constraint (such as a check constraint,
+ unique constraint, or foreign key constraint). To
+ remove a unique constraint, drop a unique index,
+ To remove other kinds of constraints you need to recreate
and reload the table, using other parameters to the
command.
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 56b722847a4..5102a2ad885 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
@@ -450,7 +450,15 @@ CREATE TABLE distributors (
[ CONSTRAINT name ] { [
NULL | NOT NULL ] | UNIQUE | PRIMARY KEY | CHECK constraint } [, ...]
+ class="parameter">constraint | REFERENCES
+ referenced table
+ (referenced column)
+ [ MATCH match type ]
+ [ ON DELETE action ]
+ [ ON UPDATE action ]
+ [ [ NOT ] DEFERRABLE ]
+ [ INITIALLY check time ] }
+ [, ...]
@@ -900,7 +908,7 @@ ERROR: Cannot insert a duplicate key into a unique index.
Only one PRIMARY KEY can be specified for a table.
-
+
Notes
@@ -920,6 +928,278 @@ ERROR: Cannot insert a duplicate key into a unique index.
</para>
</refsect3>
</refsect2>
+
+ <refsect2 id="R2-SQL-REFERENCES-1">
+ <refsect2info>
+ <date>2000-02-04</date>
+ </refsect2info>
+ <title>
+ REFERENCES Constraint
+
+
+[ CONSTRAINT name ] REFERENCES
+referenced table [ ( referenced column ) ]
+[ MATCH matchtype ]
+[ ON DELETE action ]
+[ ON UPDATE action ]
+[ [ NOT ] DEFERRABLE ]
+[ INITIALLY check time ]
+
+
+ The REFERENCES constraint specifies a rule that a column
+ value is checked against the values of another column.
+ REFERENCES can also be specified as part of
+ a FOREIGN KEY table constraint.
+
+
+
+ Inputs
+
+
+
+ CONSTRAINT name
+
+
+ An arbitrary name for the constraint.
+
+
+
+
+ referenced table
+
+
+ The table that contains the data to check against.
+
+
+
+
+ referenced column
+
+
+ The column in the referenced table
+ to check the data against. If this is not specified, the PRIMARY KEY of the
+ referenced table is used.
+
+
+
+
+ MATCH matchtype
+
+
+ The type of comparison to do between the table data. There are three
+ types of matching, MATCH FULL, MATCH PARTIAL, and the unspecified match type
+ used if no match type is specified.
+
+
+
+
+ ON DELETE action
+
+
+ The action to do when a referenced row in the referenced table is being
+ deleted. There are the following actions.
+
+
+ CASCADE
+
+
+ Delete any rows referencing the deleted row.
+
+
+
+
+ RESTRICT
+
+
+ Disallow deletion of rows being referenced.
+
+
+
+
+ SET NULL
+
+
+ Set the referencing column values to NULL.
+
+
+
+
+ SET DEFAULT
+
+
+ Set the referencing column values to their default value.
+
+
+
+
+ NO ACTION
+
+
+ Do nothing.
+
+
+
+
+
+
+
+
+ ON UPDATE action
+
+
+ The action to do when a referenced column in the referenced table is being
+ updated to a new value. If the row is updated, but the referenced column
+ is not changed, no action is done. There are the following actions.
+
+
+ CASCADE
+
+
+ Update the value of the referencing column to the new value of the
+ referenced column.
+
+
+
+
+ RESTRICT
+
+
+ Disallow update of row being referenced.
+
+
+
+
+ SET NULL
+
+
+ Set the referencing column values to NULL.
+
+
+
+
+ SET DEFAULT
+
+
+ Set the referencing column values to their default value.
+
+
+
+
+ NO ACTION
+
+
+ Do nothing.
+
+
+
+
+
+
+
+
+ [ NOT ] DEFERRABLE
+
+
+ Tells the trigger manager whether this constraint may be
+ deferred to the end of transaction.
+
+
+
+
+ INITIALLY check time
+
+
+ check time has two possible values
+ which specify the default time to check the constraint.
+
+
+ DEFERRED
+
+ Check this constraint at the end of the transaction.
+
+
+
+ IMMEDIATE
+
+ Check this constraint after each statement.
+
+
+
+
+
+
+
+
+
+
+
+
+
+ 2000-02-04
+
+
+ Outputs
+
+
+
+
+ status
+
+
+
+
+
+ERROR: name referential integrity violation - key referenced from
+table not found in referenced table
+
+
+
+ This error occurs at runtime if one tries to insert a value
+ into a column which does not have a matching column in the
+ referenced table.
+
+
+
+
+
+
+
+
+
+
+
+
+ Description
+
+ The REFERENCES column constraint specifies that a column of a
+ table must only contain values which match against values
+ in a referenced column of a referenced table.
+
+
+ A value added to this column are matched against the
+ values of the referenced table and referenced column using
+ the given match type.
+ In addition, when the referenced column data is changed,
+ actions are run upon this column's matching data.
+
+
+
+
+
+ 1998-09-11
+
+
+ Notes
+
+
+ Currently Postgres only supports
+ MATCH FULL and an unspecified MATCH type.
+ In addition, the referenced columns are supposed to be
+ the columns of a UNIQUE constraint in the referenced table,
+ however Postgres does not
+ enforce this.
+
+
@@ -930,6 +1210,14 @@ ERROR: Cannot insert a duplicate key into a unique index.
[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( column [, ...] )
[ CONSTRAINT name ] CHECK ( constraint )
+[ CONSTRAINT name ] FOREIGN KEY ( column [, ...] )
+ REFERENCES referenced table
+ (referenced column [, ...] )
+ [ MATCH ]
+ [ ON DELETE action ]
+ [ ON UPDATE action ]
+ [ [ NOT ] DEFERRABLE ]
+ [ INITIALLY check time ]
@@ -1174,7 +1462,271 @@ CREATE TABLE distributors (
information.
+
+
+
+ 2000-02-04
+
+
+ REFERENCES Constraint
+
+
+[ CONSTRAINT name ]
+FOREIGN KEY ( column [, ...] ) REFERENCES
+referenced table [ ( referenced column [, ...] ) ]
+[ MATCH matchtype ]
+[ ON DELETE action ]
+[ ON UPDATE action ]
+[ [ NOT ] DEFERRABLE ]
+[ INITIALLY check time ]
+
+
+ The REFERENCES constraint specifies a rule that a column
+ value is checked against the values of another column.
+ REFERENCES can also be specified as part of
+ a FOREIGN KEY table constraint.
+
+
+
+ Inputs
+
+
+
+ CONSTRAINT name
+
+
+ An arbitrary name for the constraint.
+
+
+
+
+ column [, ...]
+
+
+ The names of one or more columns in the table.
+
+
+
+
+ referenced table
+
+
+ The table that contains the data to check against.
+
+
+
+
+ referenced column [, ...]
+
+
+ One or more column in the referenced table
+ to check the data against. If this is not specified, the PRIMARY KEY of the
+ referenced table is used.
+
+
+
+
+ MATCH matchtype
+
+
+ The type of comparison to do between the table data. There are three
+ types of matching, MATCH FULL, MATCH PARTIAL, and the unspecified match type
+ used if no match type is specified.
+
+
+
+
+ ON DELETE action
+
+
+ The action to do when a referenced row in the referenced table is being
+ deleted. There are the following actions.
+
+
+ CASCADE
+
+
+ Delete any rows referencing the deleted row.
+
+
+
+
+ RESTRICT
+
+
+ Disallow deletion of rows being referenced.
+
+
+
+
+ SET NULL
+
+
+ Set the referencing column values to NULL.
+
+
+
+
+ SET DEFAULT
+
+
+ Set the referencing column values to their default value.
+
+
+
+
+ NO ACTION
+
+
+ Do nothing.
+
+
+
+
+
+
+
+
+ ON UPDATE action
+
+
+ The action to do when a referenced column in the referenced table is being
+ updated to a new value. If the row is updated, but the referenced column
+ is not changed, no action is done. There are the following actions.
+
+
+ CASCADE
+
+
+ Update the value of the referencing column to the new value of the
+ referenced column.
+
+
+
+
+ RESTRICT
+
+
+ Disallow update of row being referenced.
+
+
+
+
+ SET NULL
+
+
+ Set the referencing column values to NULL.
+
+
+
+
+ SET DEFAULT
+
+
+ Set the referencing column values to their default value.
+
+
+
+
+ NO ACTION
+
+
+ Do nothing.
+
+
+
+
+
+
+
+
+ [ NOT ] DEFERRABLE
+
+
+ Tells the trigger manager whether this constraint may be
+ deferred to the end of transaction.
+
+
+
+
+ INITIALLY check time
+
+
+ check time has two possible values
+ which specify the default time to check the constraint.
+
+
+ DEFERRED
+
+ Check this constraint at the end of the transaction.
+
+
+
+ IMMEDIATE
+
+ Check this constraint after each statement.
+
+
+
+
+
+
+
+
+
+
+
+ 2000-02-04
+
+
+ Outputs
+
+
+
+
+ status
+
+
+
+
+
+ERROR: name referential integrity violation - key referenced from
+table not found in referenced table
+
+
+
+ This error occurs at runtime if one tries to insert a value
+ into a column which does not have a matching column in the
+ referenced table.
+
+
+
+
+
+
+
+
+
+
+
+ Description
+
+ The FOREIGN KEY constraint specifies a rule that a group of one
+ or more distinct columns of a table are related to a group
+ of distinct columns in the referenced table.
+
+
+
+ The FOREIGN KEY table constraint is similar to that for column constraints,
+ with the additional capability of encompassing multiple columns.
+
+
+ Refer to the section on the FOREIGN KEY column constraint for more
+ information.
+
+
+
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 14a986d6a49..401c41e4021 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1,5 +1,5 @@
@@ -362,6 +362,16 @@ dumpSequence(table): SELECT failed
Large objects are ignored and must be dealt with manually.
+
+
+
+ When doing a data only dump, pg_dump emits queries
+ to disable triggers on user tables before inserting the data and queries to
+ reenable them after the data has been inserted. If the restore is stopped
+ in the middle, the system catalogs may be left in the wrong state.
+
+
+
--
cgit v1.2.3