db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: identity column causes grief when populating table - suggestions?
Date Tue, 22 Feb 2005 21:55:53 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
I agree Derby could use <b>generated by DEFAULT</b> option. But since
it is not there currently, omitting the column in the insert list could
help where possible. <br>
<br>
Satheesh<br>
<br>
Suavi Ali Demir wrote:<br>
<blockquote cite="mid20050222204352.31319.qmail@web53009.mail.yahoo.com"
 type="cite">
  <pre wrap="">The next import may be a table with foreign keys to
the first one. Sometimes (most of the time actually)
it is desireable to keep the original values in an
identity column. 

Having to use "generated always" blocks many solutions
including import/export, replication/sync where key
space could be partitioned between master slave to be
able to replicate identity columns back and forth.
(Want to have 2 databases where in one database i
insert values 0-2 billion and in another i insert 2
billion to 4 billion and i want these rows to be
usable in both databases). "generated by default" may
help greatly to make this possible for auto int
columns.
Regards,
Ali

--- Satheesh Bandaram <a class="moz-txt-link-rfc2396E" href="mailto:satheesh@Sourcery.Org">&lt;satheesh@Sourcery.Org&gt;</a>
wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">You are trying to insert into a column that is
declared as identity.
Database automatically generates a unique number for
identity columns,
so just leave out ID column in your INSERT
statement, like:

    insert into sample (DESCRIPTION, QUANTITY)
values ('blah', 1.1);

That should fix it...

Satheesh

Trevor Squires wrote:

    </pre>
    <blockquote type="cite">
      <pre wrap="">Hi,

I've been googling all morning and can't find info
      </pre>
    </blockquote>
    <pre wrap="">to solve this
    </pre>
    <blockquote type="cite">
      <pre wrap="">problem (using derby of course), I hope someone
      </pre>
    </blockquote>
    <pre wrap="">here can help...
    </pre>
    <blockquote type="cite">
      <pre wrap="">As I write my application I have a script which
      </pre>
    </blockquote>
    <pre wrap="">blats and recreates my
    </pre>
    <blockquote type="cite">
      <pre wrap="">database and then populates the tables with test
      </pre>
    </blockquote>
    <pre wrap="">data.
    </pre>
    <blockquote type="cite">
      <pre wrap="">However, many of my tables have identity columns
      </pre>
    </blockquote>
    <pre wrap="">and it's causing the
    </pre>
    <blockquote type="cite">
      <pre wrap="">following error when I insert the data:

 11:06:26  [INSERT - 0 row(s), 0.016 secs]  [Error
      </pre>
    </blockquote>
    <pre wrap="">Code: 30000, SQL
    </pre>
    <blockquote type="cite">
      <pre wrap="">State: 42Z23]  Attempt to modify an identity
      </pre>
    </blockquote>
    <pre wrap="">column 'ID'.
    </pre>
    <blockquote type="cite">
      <pre wrap="">Here's my table:

create table sample (
    id int not null generated always as identity,
    description varchar(128) not null,
    quantity double not null,
        constraint product_pk primary key (id)
);

Here's my insert statement

insert into sample (ID, DESCRIPTION, QUANTITY)
      </pre>
    </blockquote>
    <pre wrap="">values (1, 'blah', 1.1);
    </pre>
    <blockquote type="cite">
      <pre wrap="">I've tried to create the table without "generated
      </pre>
    </blockquote>
    <pre wrap="">always as identity"
    </pre>
    <blockquote type="cite">
      <pre wrap="">and then altering the table after the insert(s).

Unfortunately I can't get the syntax right and I
      </pre>
    </blockquote>
    <pre wrap="">can't figure out if
    </pre>
    <blockquote type="cite">
      <pre wrap="">there's a way to turn off identity generation
      </pre>
    </blockquote>
    <pre wrap="">temporarily.
    </pre>
    <blockquote type="cite">
      <pre wrap="">Does anyone have any suggestions like pointer to
      </pre>
    </blockquote>
    <pre wrap="">the right way to do
    </pre>
    <blockquote type="cite">
      <pre wrap="">this or a relatively painless workaround?

Thanks for listening,
Trevor



      </pre>
    </blockquote>
    <pre wrap="">
    </pre>
  </blockquote>
  <pre wrap=""><!---->


  </pre>
</blockquote>
</body>
</html>


Mime
View raw message