auto increment - How to AUTO_INCREMENT in db2?

Question

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.

Answer 1

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.