Re: insert different from union?
Date: Mon, 26 Jan 2004 15:40:00 -0500
Message-ID: <d_ednSQFv-Lb5ojd4p2dnA_at_golden.net>
"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
news:bv3ntf$rqa$1_at_gazette.almaden.ibm.com...
> "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...
If you are trying to make the point that triggered procedures are not yet well-understood, I agree. The principle of cautious design suggests minimizing their use.
First, we need an assignment triggered procedure. Then, if we can define appropriate short-hands corresponding to update, delete and insert triggered procedures, we should define them using those short-hands. Received on Mon Jan 26 2004 - 21:40:00 CET