Re: Modifying INSERT

Laconic2 wrote:

> Let's say that a USER (programmed or human) does an INSERT of a new table
> row that duplicates, column for column, the values in an existing row.
>
> Present day DBMS products act differently, depending on whether the table
> has a primary key or not.
>
> If there is no primary key, the INSERT creates a duplicate row.
>
> If there is a primary key, the insert fails.
>
> But how about a different behaviour? What if the DBMS reported success back
> to the writer, but didn't write anything to the table? Essentially, the
> new value of the table is the UNION of the existing table and the inserted
> row?
>
> What would the consequences be of this behaviour?

Things would be more nearly truly relational?

Date's description of updating union views would need fewer weasel words about "if the row inserted still does not appear"?

I think it would be courteous to warn (but not error) if the cardinality of the final relvar is not the sum of the cardinalities of the source relvar and the new material - but it shouldn't be an error IMO. OTOH, if you're dealing with temporal data, inserting a new record can produce a result table with fewer rows than the source table -- see Temporal Data and the Relational Model for details.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/