Re: PATCH proposed with new features for CREATE TABLE - Mailing list pgsql-hackers

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
thelack of the referenced
 
> tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion
sinceit 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
introduceany kind of
 
> indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending
onthe order of creation of
 
> the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing
tableis updated or tuples
 
> are added to it, even when this table is the origin of various referential chains of verifications, the resulting
behavioronly depends on the
 
> order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem
forwhich no database product
 
> includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples
froma 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
newvalue, 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,
noaction 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
newfk_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
otherattributes 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)
againstthe 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
anddo 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@exa.unicen.edu.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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026