Re: insert different from union?

Bob Badour wrote:

> "Marshall Spight" <mspight_at_dnai.com> wrote in message
> news:S0cRb.27298$U%5.179610_at_attbi_s03...
> 

> 
> 
> Insert is a short-hand for a constrained assignment statement. I don't know
> what made you think it is primitive or what made you think it is equivalent
> to an unconstrained assignment statement.
> 
> A insert B is equivalent to a series of preconditions of the form:
> PRE( ( A{candidate key} intersect B{candidate key} ){} = DUM )
> for each candidate key of A followed by an assignment of the form:
> A := A union B
> 
> Thus, insert is different from union and has the added benefit of treating
> certain likely errors as errors. I suggest that both insert and assignment
> are useful operations, and I would prefer a dbms that provides both.

I have sympathy with Marshall's view.

One point that seems telling to me is how much simpler the Date & McGoveran view updating algorithm would be if it didn't have to keep on stipulating "unless it has already been inserted in RelvarB as a result (side effect) of the operation on RelvarA" - when there are two relvars involved in the operation under consideration.

That is, if the behaviour of insert was more like the unconstrained union with assignment than the constrained union with assignment that Bob cites, then most people would be better off most of the time.

After the operation is complete, it might be helpful to know the difference between the cardinality of the result and the sum of the cardinalities of the operands (how many duplicates there were).

And as Marshall pointed out, if the key columns (for N >= 1 candidate keys) are identical, then the whole tuple must be identical or an error will be generated. So, the only time you don't get the same behaviour between the constrained and unconstrained versions is when you would otherwise be inserting duplicate data. The union operation harmlessly eliminates the duplicates, so the result is perfectly OK.

Of course, SQL permits duplicates in general - that may be why insert needs to be different there. But in a TRDBMS, is that extra qualification (Bob's pre-condition) really helpful? I tend to think not.

-- 
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/