CODEWITHSUNDEEP
db2ibm-cloud
Lonely Vagrant
Lonely Vagrant

Reputation: 137

How can I write queries for the Bluemix SQL database

How can I create a table that includes auto increment and NOT NULL. I am using the sql database console that bluemix offers. The following query gives me an error.

CREATE TABLE discounts (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  expired_date DATE NOT NULL,
  amount DECIMAL(10,2) NULL,
  PRIMARY KEY (id)
);

The error is:

DDL failed with message _ Exception. _ state = 42601; error code = -104; error Message = Error for batch element #1: An unexpected token "," was found following "NULL AUTO_INCREMENT". Expected tokens may include: "".. _CODE=-104, _STATE=42601, DRIVER=3.66.46

Upvotes: 1

Views: 337

Answers (1)

James Thomas
James Thomas

Reputation: 4339

DB2 does not support the "AUTO_INCREMENT" statement.

You can use the "GENERATED ALWAYS AS IDENTITY" command instead.

CREATE TABLE discounts ( id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), title VARCHAR(255) NOT NULL, expired_date DATE NOT NULL, amount DECIMAL(10,2) NULL, PRIMARY KEY (id) );

Further details about creating automatic values are detailed here

Upvotes: 1

Related Questions