Re: insert different from union?

"Bob Badour" <bbadour_at_golden.net> wrote in message news:7fydnWcUn64Bz4jd4p2dnA_at_golden.net...
> "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.

Agreed.

A possibly more interesting question is, should ON INSERT triggers be fired when someone does

A := A union B

?

And then what about on

A := B

If A equaled { <1, 1>, <2,2> }
and B equals { <2,1>}

Should we have two DELETE and one INSERT triggers fire, or one DELETE and one UPDATE. If the latter, which row in A does which trigger fire one?

If you say, triggers don't fire on assignment, what about if I do

PRE( ( A{candidate key} intersect B{candidate key} ){} = DUM ) A := A union B

Why should that not fire triggers when the identical

INSERT INTO A VALUES B would.

Or would it...

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Mon Jan 26 2004 - 19:53:26 CET