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) )