44

I thought this would be simple, but I can't seem to use AUTO_INCREMENT in my db2 database. I did some searching and people seem to be using "Generated by Default", but this doesn't work for me.

If it helps, here's the table I want to create with the sid being auto incremented.

  create table student(
      sid integer NOT NULL <auto increment?>
      sname varchar(30),
      PRIMARY KEY (sid)
      );

Any pointers are appreciated.

Matt
  • 5,408
  • 14
  • 52
  • 79

4 Answers4

73

You're looking for is called an IDENTITY column:

create table student (
   sid integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
  ,sname varchar(30)
  ,PRIMARY KEY (sid)
);

A sequence is another option for doing this, but you need to determine which one is proper for your particular situation. Read this for more information comparing sequences to identity columns.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • Thanks Ian. This is where I started. I'm not sure why, but for whatever reason the "GENERATED ALWAYS" part of this query isn't recognized. I get the error missing right parens... an indicator it's not recog the keywords. – Matt Nov 20 '12 at 06:21
  • What version of DB2 are you running, and on what platform? – Ian Bjorhovde Nov 20 '12 at 07:44
  • You're missing a comma (`','`) after the declaration of the identity sequence, but it otherwise works on _my_ DB2 deployment (I haven't turned on journaling, though, so I can't declare the primary key at the moment). – Clockwork-Muse Nov 20 '12 at 17:10
  • 3
    Should you ever need to manually insert a value into the column, you would change the GENERATED ALWAYS to GENERATED BY DEFAULT. – Leo Nov 20 '12 at 20:37
  • Is IDENTITY same as SERIAL in other RDBMS ? – valijon May 06 '19 at 12:50
  • 2
    @valijon If you want to be technical, they are not really the same, because IDENTITY is an attribute of a column, just like AUTO_INCREMENT. SERIAL is a pseudo-type, which specifies both a column’s type and its attributes. – Ian Bjorhovde May 07 '19 at 22:29
  • As of today (24th oct 2022) both links redirect to a generic page of IBM products. Here are the updated links : [determine which is proper](https://www.ibm.com/docs/en/db2/11.5?topic=sequences-determining-when-use-identity-columns) and [comparing sequences to identity columns](https://www.ibm.com/docs/en/db2/11.5?topic=sequences-compared-identity-columns) – ToddEmon Oct 24 '22 at 12:42
6

You will have to create an auto-increment field with the sequence object (this object generates a number sequence).

Use the following CREATE SEQUENCE syntax:

  CREATE SEQUENCE seq_person
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 10

The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

  INSERT INTO Persons (P_Id,FirstName,LastName)
  VALUES (seq_person.nextval,'Lars','Monsen')

The SQL statement above would insert a new record into the "Persons" table. The "P_Id" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

Matt
  • 5,408
  • 14
  • 52
  • 79
  • While I'll grant that DB2 has sequences, and this is one use for them (If I remember right, auto-gen columns may use the same type of object under the covers), DB2 isn't listed on that page... Did you just copy the Oracle setup? Keep in mind that there are several _other_ big differences between Oracle and DB2 (what depends on version), so you can't just blindly copy. (I think I'm finding people's lack of faith in w3schools rather justified here: not only are they missing DB2, but also postgreSQL and Derby, all of which are commonly used... maybe more than Access) – Clockwork-Muse Nov 20 '12 at 17:09
  • @Clockwork-Muse - you are correct while this kind of construct will work in the db2 the syntax is different. – Hogan May 24 '16 at 19:29
2

Added a few optional parameters for creating "future safe" sequences.

CREATE SEQUENCE <NAME>
  START WITH 1
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 10;
Nelson
  • 27
  • 6
noname
  • 31
  • 1
1

hi If you are still not able to make column as AUTO_INCREMENT while creating table. As a work around first create table that is:

create table student( sid integer NOT NULL sname varchar(30), PRIMARY KEY (sid) );

and then explicitly try to alter column bu using the following

alter table student alter column sid set GENERATED BY DEFAULT AS IDENTITY

Or

alter table student alter column sid set GENERATED BY DEFAULT AS IDENTITY (start with 100)

Mohd Arshil
  • 195
  • 1
  • 1
  • 9