On IBM DB2 I try the following direct SQL:
CREATE TABLE users (
user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(60),
PRIMARY KEY (user_id)
)
Then I get the following error:
The table definition statement failed because some functionality was specified in the table definition that is not supported with the table type. Unsupported functionality: "GENERATED".. SQLCODE=-1666, SQLSTATE=42613, DRIVER=3.63.123 SQL Code: -1666, SQL State: 42613
GENERATED BY DEFAULT is supposed to be supported according to IBM DB2 Documentation.
Is there something incorrect in my SQL?
EDIT:
I used sqlalchemy and ibm_db_sa with the following code:
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String(16), nullable=False),
Column('email_address', String(60), key='email')
)
which produces:
CREATE TABLE users (
user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(60),
PRIMARY KEY (user_id)
)
However, by adding autoincrement=False the IDENTITY is removed and the SQL executes without problem:
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True, autoincrement=False),
Column('user_name', String(16), nullable=False),
Column('email_address', String(60), key='email')
)
which produces:
CREATE TABLE users2 (
user_id INT NOT NULL,
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(60),
PRIMARY KEY (user_id)
)