Re: insert different from union?

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:JLoRb.29146$zj7.25503_at_newsread1.news.pas.earthlink.net...
> Bob Badour wrote:
>
> > "Marshall Spight" <mspight_at_dnai.com> wrote in message
> > news:S0cRb.27298$U%5.179610_at_attbi_s03...
> >
> >>Hi all,
> >>
> >>I consider the SQL "insert" statement. It doesn't look like
> >>a primitive to me; it seems like a special case of the union
> >>operator.
> >>
> >>But maybe not:
> >>
> >>create table test (id int primary key);
> >>insert into test values (1);
> >>insert into test values (1);
> >>
> >>If the insert statement was really the same thing as a union,
> >>then the second insert would be no problem. Instead, I get:
> >>
> >> ERROR: duplicate key violates unique constraint "test_pkey"
> >>
> >>Now, if I had a table with a primary key and other attributes,
> >>and I inserted something with the primary key the same as
> >>an existing row and some/all of the additional attributes
> >>different, then I would expect an error. But in this case
> >>I get an error even though there's no incompatibility with
> >>union.
> >>
> >>So I ask you: another "sql stupid" or is it something I'm not
> >>seeing? Is it right for insert to be its own primitive with its
> >>own semantics, or should it be just a specialization of
> >>"set = set union row"?
> >>
> >>Which way is more useful?
> >
> >
> > 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.

If you don't want the preconditions, use the union assignment statement. If you want the semantics of inserting something, use the Insert shorthand.

You might be able to sway me to your point of view, but I would need a better argument than you have given thus far.

Consider "insert using" and intervals. How does the cardinality help? Received on Tue Jan 27 2004 - 15:06:04 CET