To get this applied, we will need to hear from people who want this
functionality. Sorry.
> 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
>
--
Bruce Momjian | http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
