CODEWITHSUNDEEP
sqldatabasedb2
Vivek Ranjan
Vivek Ranjan

Reputation: 1492

Alter primary key sequence( 'Default' to 'Always') in DB2

I have created the below table in DB2. Since the sequence is GENERATED BY DEFAULT, the primary key can be inserted in table(as the primary key is added after table creation with alter) and there is chance of duplicate values. The solution is to create the sequence by using GENERATED BY ALWAYS. Now I have data in table and I want to alter it from GENERATED BY DEFAULT to GENERATED BY ALWAYS

  CREATE TABLE HW_COB.ILCCDS 
  ( 
                   CDS_IDENTITY FOR COLUMN CDSID NUMERIC(13, 0) GENERATED BY DEFAULT AS IDENTITY ( 
                   START WITH 1 INCREMENT BY 1 
                   NO MINVALUE NO MAXVALUE 
                   NO CYCLE NO ORDER 
                   CACHE 20 ) ,

CODE_1 FOR COLUMN CDSCD1 CHAR(30),
CODE_2 FOR COLUMN CDSCD2 CHAR(30) );
ALTER TABLE HW_COB.ILCCDS ADD CONSTRAINT HW_COB.PK_CDS_IDENTITY PRIMARY KEY (CDS_IDENTITY);

Upvotes: 1

Views: 1489

Answers (1)

Vivek Ranjan
Vivek Ranjan

Reputation: 1492

Finally got the alter as :

ALTER TABLE HW_COB.ILCCDS ALTER COLUMN CDS_IDENTITY SET GENERATED ALWAYS;

Upvotes: 2

Related Questions