Add a auto increment primary key to existing table in oracle [duplicate]

If you have the column and the sequence, you first need to populate a new key for all the existing rows. Assuming you don't care which key is assigned to which row

UPDATE table_name SET new_pk_column = sequence_name.nextval; 

Once that's done, you can create the primary key constraint (this assumes that either there is no existing primary key constraint or that you have already dropped the existing primary key constraint)

ALTER TABLE table_name ADD CONSTRAINT pk_table_name PRIMARY KEY( new_pk_column ) 

If you want to generate the key automatically, you'd need to add a trigger

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN :new.new_pk_column := sequence_name.nextval; END; 

If you are on an older version of Oracle, the syntax is a bit more cumbersome

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN SELECT sequence_name.nextval INTO :new.new_pk_column FROM dual; END; 

Snagged from Oracle OTN forums

Use alter table to add column, for example:

alter table tableName add(columnName NUMBER); 

Then create a sequence:

CREATE SEQUENCE SEQ_ID START WITH 1 INCREMENT BY 1 MAXVALUE 99999999 MINVALUE 1 NOCYCLE; 

and, the use update to insert values in column like this

UPDATE tableName SET columnName = seq_test_id.NEXTVAL 

You can use the Oracle Data Modeler to create auto incrementing surrogate keys.

Step 1. - Create a Relational Diagram

You can first create a Logical Diagram and Engineer to create the Relational Diagram or you can straightaway create the Relational Diagram.

Add the entity (table) that required to have auto incremented PK, select the type of the PK as Integer.

Step 2. - Edit PK Column Property

Get the properties of the PK column. You can double click the name of the column or click on the 'Properties' button.

Column Properties dialog box appears.

Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.

Step 3. - Additional Information

Additional information relating to the auto increment can be specified by selecting the 'Auto Increment' tab.

• Start With
• Increment By
• Min Value
• Max Value
• Cycle
• Disable Cache
• Order
• Sequence Name
• Trigger Name
• Generate Trigger

It is usually a good idea to mention the sequence name, so that it will be useful in PL/SQL.

Click OK (Apply) to the Column Properties dialog box.

Click OK (Apply) to the Table Properties dialog box.

Table appears in the Relational Diagram.