I have a table with following schema in my DB2 database.
CREATE TABLE IDN_OAUTH_CONSUMER_APPS ( CONSUMER_KEY VARCHAR (255) NOT NULL, CONSUMER_SECRET VARCHAR (512), USERNAME VARCHAR (255), TENANT_ID INTEGER DEFAULT 0, APP_NAME VARCHAR (255), OAUTH_VERSION VARCHAR (128), CALLBACK_URL VARCHAR (1024), GRANT_TYPES VARCHAR (1024) /
I need to add a new column ID of Type integer not null auto increment, and make it the primary key. How can I do that without deleting the table?
Advertisement
Answer
I could do this successfully using following set of queries.
ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD COLUMN ID INTEGER NOT NULL DEFAULT 0 CREATE SEQUENCE IDN_OAUTH_CONSUMER_APPS_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE CREATE TRIGGER IDN_OAUTH_CONSUMER_APPS_TRIGGER NO CASCADE BEFORE INSERT ON IDN_OAUTH_CONSUMER_APPS REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (NEW.ID) = (NEXTVAL FOR IDN_OAUTH_CONSUMER_APPS_SEQUENCE); END REORG TABLE IDN_OAUTH_CONSUMER_APPS UPDATE IDN_OAUTH_CONSUMER_APPS SET ID = IDN_OAUTH_CONSUMER_APPS_SEQUENCE.NEXTVAL
And then add primary key using alter table.