Re: PATCH proposed with new features for CREATE TABLE

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 (jozzano(at)exa(dot)unicen(dot)edu(dot)ar)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026