Hi people!.I developed a work for an university course, which I wish to share with
you.
I extended the foreign key clause in the create table in order to permit insertions
and updates on a referencing table (a table with foreign key
attributes) with all kind of actions (the existing ones plus CASCADE, SET NULL and SET
DEFAULT).
I think it is important to handle situations where the referencing data is available
but it cannot be inserted due to the lack of the referenced
tuple. It is ugly, for example, to request the user to create a dummy referenced entry
previous to the insertion since it can be done
automatically with the proposed functionality.
Applying it in the context of a product with a well-defined execution model of
triggers, like PostgreSQL, I do not introduce any kind of
indetermination in the sequence of verification of the referential constraints,
because we know beforehand, depending on the order of creation of
the tables and constraints, which will be the resulting order of the chain of
verifications. So, when a referencing table is updated or tuples
are added to it, even when this table is the origin of various referential chains of
verifications, the resulting behavior only depends on the
order of creation mentioned above. (I insist with the theme of determinism because I
think this is the main problem for which no database product
includes this characteristic). I tested the code with examples of such cases (taking
modified problematical examples from a text of Markowitz)
and it works well.
The new syntax for the column_constraint_clause (and table_constraint_clause) of the
CREATE TABLE statement that I propose (and implement) is:
...
[ ON INSERT action ]
[ ON DELETE action ]
[ ON UPDATE_LEFT action ]
[ ON UPDATE_RIGHT action ]
...
where
"ON DELETE action"
stays the same as before (it refers to deletes in the referenced table),
"ON UPDATE_RIGHT action"
is the original ON UPDATE action (like before, it refers to modifications in the
referenced table),
"ON UPDATE_LEFT action"
specifies the action to do when a referencing column (a FK_column) in the
referencing table is being updated to a new value, and this new
value do not exist like pk_value in the pk_table. If the row is updated, but the
referencing column is not changed, no action is done. There are
the following actions.
NO ACTION
Disallows update of row.
RESTRICT
Disallows update of row.
CASCADE
Updates the value of the referenced column (the pk_column) to the new value
of the
referencing column (the fk_column).
SET NULL
Sets the referencing column values to NULL.
SET DEFAULT
Sets the referencing column values to their default value.
"ON INSERT action"
specifies the action to do when a referencing row (a FK_row) in the referencing
table is being inserted, and the new fk_values do not exist
like pk_values in the referenced table (pk_table). There are the following actions.
NO ACTION
Disallows insert of row.
RESTRICT
Disallows insert of row.
CASCADE
Inserts a new row into the referenced table which pk_columns take the values
of the new fk_columns, and the other attributes are set to
NULL values (if it is allowed).
SET NULL
Sets the referencing column values to NULL.
SET DEFAULT
Sets the referencing column values to their default value.
I have not added new files, just modified the existing ones (so the makefiles stay
like before). I send a diff (-c) against the version 7.0.2
(the one I worked with).
In summary, the patch contains:
* modifications to the grammar to include the new syntax of the CREATE TABLE statement
(to recognize the new tokens and do the appropriate
stuff).
* Addition of definitions of flags and masks for FOREIGN KEY constraints in CreateStmt.
* the new generic trigger procedures for referential integrity constraint checks.
* modifications to the parser stage to accept them (in procedures
transformCreateStmt() and
transformAlterTableStmt() ).
* update to declarations for operations on built-in types.
* extension of the definition of the system "procedure" relation (pg_proc) along with
the
relation's initial contents.
* modifications to the TRIGGERs support code to accept the new characteristics.
Many thanks in advance to those who read and (maybe) consider all this, regards
Jose Luis Ozzano ([EMAIL PROTECTED])
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
