Can someone comment on this? > 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
