db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: (Derby 167) was Re: [Patch] Re: About improvement of DERBY-134
Date Tue, 05 Apr 2005 19:49:51 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Tomohito,<br>
<br>
I thought a column that is GENERATED <b>BY DEFAULT</b> AS IDENTITY
would allow you to insert any user specified value. System would
generate a value only if there is no explicit value provided, so I am
not sure if there is a need to disable "identity" feature for the
column. I looked very briefly at the SQL spec. This is what it says: If
&lt;identity column specification&gt; is specified,then:<br>
<ul>
  <li>If ALWAYS is specified, then an indication that values are always
generated.</li>
  <li>If BY DEFAULT is specified, then an indication that values are
generated by default.</li>
</ul>
Derby already has GENERATED ALWAYS option. I think it would be great to
add GENERATED BY DEFAULT option.<br>
<br>
I ran some sample querries on DB2 that I have access to. This is what
it does.<br>
<br>
CREATE TABLE <b>TBL1</b>(COL1 INT GENERATED <b>ALWAYS AS IDENTITY</b>,
COL2 INT)<br>
CREATE TABLE <b>TBL2</b>(COL1 INT GENERATED <b>BY DEFAULT AS IDENTITY</b>,
COL2 INT)<br>
<br>
CASE1: Any insert value for COL1 is not specified explicitly or
provided as DEFAULT.<br>
<br>
INSERT INTO TBL1(COL2) VALUES(10)<br>
INSERT INTO TBL2(COL2) VALUES(10)<br>
INSERT INTO TBL1(COL1, COL2) VALUES(DEFAULT, 10)<br>
INSERT INTO TBL2(COL1, COL2) VALUES(DEFAULT, 10)<br>
<br>
In this case, all of above-mentioned insert statements set COL1 to a
sequence value.<br>
<br>
CASE2: An insert value for COL1 is supplied explicitly by a table value
constructor.<br>
<br>
INSERT INTO TBL1(COL1, COL2) VALUES(1, 10)<br>
INSERT INTO TBL2(COL1, COL2) VALUES(1, 10)<br>
<br>
In this case, the first insert fails, but the second passes. Second
insert may cause a duplicate value to be inserted into the identity
column. If needed, a unique or primay key constraint can be used to
prevent duplicate values for the case of GENERATED BY DEFAULT option.<br>
<br>
CASE3: An update value that is not a default specification is specified
for COL1.<br>
<br>
UPDATE TBL1 SET COL1 = 1 WHERE COL2 = 10<br>
UPDATE TBL2 SET COL1 = 1 WHERE COL2 = 10<br>
<br>
First update statement raises an error. (case of GENERATED AS ALWAYS)
Second update succeeds. (GENERATED BY DEFAULT). Again, this update may
cause duplicate COL1 values, but that could be prevented by using a
unique or primay key constraint.<br>
<br>
Satheesh<br>
<br>
TomohitoNakayama wrote:
<blockquote cite="mid002201c539ce$b3759180$2000a8c0@Arkat" type="cite">Hello.
  <br>
  <br>
  <blockquote type="cite">
    <blockquote type="cite">2:being able to disable the "identity"
feature for a column
      <br>
3:being able to generate a column as non identity and after data is
      <br>
populated, alter table to add the "identity" to the column
      <br>
    </blockquote>
  </blockquote>
  <br>
I think those are needed to allow user to enable loading data "AND"
keep values at identity column tidy.
  <br>
(Now "tidy" means user can't decide pk value at their discretion.)
  <br>
  <br>
We should have both approaches after all ...
  <br>
  <br>
Then I think this is matter of priority.
  <br>
  <br>
/*
  <br>
  <br>
        Tomohito Nakayama
  <br>
        <a class="moz-txt-link-abbreviated" href="mailto:tomoihto@rose.zero.ad.jp">tomoihto@rose.zero.ad.jp</a>
  <br>
        <a class="moz-txt-link-abbreviated" href="mailto:tomonaka@basil.ocn.ne.jp">tomonaka@basil.ocn.ne.jp</a>
  <br>
  <br>
        Naka
  <br>
        <a class="moz-txt-link-freetext" href="http://www5.ocn.ne.jp/~tomohito/TopPage.html">http://www5.ocn.ne.jp/~tomohito/TopPage.html</a>
  <br>
  <br>
*/
  <br>
----- Original Message ----- From: "Kathey Marsden"
<a class="moz-txt-link-rfc2396E" href="mailto:kmarsdenderby@sbcglobal.net">&lt;kmarsdenderby@sbcglobal.net&gt;</a>
  <br>
To: "Derby Development" <a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
  <br>
Sent: Sunday, April 03, 2005 11:12 PM
  <br>
Subject: Re: (Derby 167) was Re: [Patch] Re: About improvement of
DERBY-134
  <br>
  <br>
  <br>
  <blockquote type="cite">TomohitoNakayama wrote:
    <br>
    <br>
    <blockquote type="cite">Hello.
      <br>
      <br>
I was interested in issue of Derby-167...
      <br>
      <br>
I recognized there was proposed three options.
      <br>
1:being able to "generate by default as identity"
      <br>
2:being able to disable the "identity" feature for a column
      <br>
3:being able to generate a column as non identity and after data is
      <br>
populated, alter table to add the "identity" to the column
      <br>
      <br>
In my opinion , option 2 and option 3  increase statuses which table
      <br>
can take , identity feature working state and not working state,
      <br>
so they would make it complex to maintain db.
      <br>
On the other hand, if we take option 1, we need to prevent it from to
      <br>
have same value in identity column.
      <br>
      <br>
    </blockquote>
I think it's ok to let the user prevent duplicates  with a primary key
    <br>
if they want to.
    <br>
When creating the table,  the user would need to make sure that they
    <br>
used START WITH  to ensure that generation starts with the maximum
value
    <br>
+ 1.  I put a comment in the bug with how I think the process would go.
    <br>
    <br>
A bit precarious I know if you forget to create your table properly,
but
    <br>
at least it makes it *possible* to load data.
    <br>
Later we could enhance support for ALTER TABLE to alter the identity
    <br>
column specification to specify a RESTART WITH value.
    <br>
    <br>
Thanks
    <br>
    <br>
    <br>
Kathey
    <br>
    <br>
    <br>
    <br>
    <br>
    <br>
    <br>
    <br>
-- <br>
No virus found in this incoming message.
    <br>
Checked by AVG Anti-Virus.
    <br>
Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 2005/04/01
    <br>
    <br>
  </blockquote>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message