0

The problem

I have a table for some data that has an ID column of type integer (which is also the primary key).

When a new data entry is added to the table, it should get a new ID whereas the ID is not known by the application that inserts the object but it should be given by the database. For example, the IDs should be assigned like 0, 1, 2, ...

Assume that I have all other data for the new entry, how would I do the insert? Normally:

insert into T values(123, 'data');

But now I don't know what to put instead of 123 - would you create some kind of global variable NEXTID in the database that provides the IDs and query/update this value each time before inserting into T?

The questions

  1. How to handle this kind of problem? A solution that is concurrency save is preferable.
  2. How to achieve this with Java/myBatis? I Have a Java class that corresponds to the table structure and a new object should be added to the database, getting a new ID automatically.

Update

What I searched for was auto-increment.

  • Is there a standard SQL way (database independent) of declaring a column as auto-increment? I am using Apache Derby and GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) is suggested here.
  • How does the insert to a table that contains auto-increment columns look like?
  • What is the best way to get the created auto-increment value after an insert when simultaneaous access to the database is possible?

I'll accept an answer that includes explanation and SQL instructions for declaration and insertion :)

Community
  • 1
  • 1
user905686
  • 4,491
  • 8
  • 39
  • 60
  • 4
    You need to declare the column as auto-increment and don't give any value to "id" field. the value will be generated automatically. – BobTheBuilder Mar 10 '13 at 14:50
  • Possible duplicate http://stackoverflow.com/questions/9414826/sql-server-how-to-add-new-identity-column-and-populate-column-with-ids – DevelopmentIsMyPassion Mar 10 '13 at 15:06
  • Also, it is never a bad idea to explicitly specify the column names you are populating: `INSERT INTO tablename (columnname1, columnname2, ...) VALUES (value1, value2, ...)`. It is especially important in some SQL products when you need to omit the auto-increment column. – Andriy M Mar 10 '13 at 15:22
  • @AshReva It's no duplicate but the question you linked is a good support to possible solutions. Thanks! – user905686 Mar 11 '13 at 08:57

2 Answers2

0

If you are using sqlserver, making column of identity type will solve the purpose something like this . 

  ALTER TABLE [dbo].[T] ADD [Column1] INT identity (1, 1)

For others like oracle you can for simple database sequence.

M Sach
  • 33,416
  • 76
  • 221
  • 314
0

In MySQL you can use 

ALTER TABLE table_name ADD id INT AUTO_INCREMENT;

this auto increment the id column, you don't have to give in insert.

Sharad
  • 3,562
  • 6
  • 37
  • 59