Re: insert different from union?

"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. Received on Mon Jan 26 2004 - 18:46:31 CET