how to set auto increment column with sql developer

Question

How do I set a column to increment automatically with Oracle SQL Developer? Why is the form disabled?

Note: The image shows the Data Modeler, but the question and top answer talk about editing an existing database.

Answer 1

If you want to make your PK auto increment, you need to set the ID column property for that primary key.

1. Right click on the table and select "Edit".
2. In "Edit" Table window, select "columns", and then select your PK column.
3. Go to ID Column tab and select Column Sequence as Type. This will create a trigger and a sequence, and associate the sequence to primary key.

See the picture below for better understanding.

// My source is: http://techatplay.wordpress.com/2013/11/22/oracle-sql-developer-create-auto-incrementing-primary-key/

Answer 2

Unfortunately oracle doesnot support auto_increment like mysql does. You need to put a little extra effort to get that.

say this is your table -

CREATE TABLE MYTABLE (   ID NUMBER NOT NULL,   NAME VARCHAR2(100)   CONSTRAINT "PK1" PRIMARY KEY (ID) ); 

You will need to create a sequence -

CREATE SEQUENCE S_MYTABLE START WITH 1 INCREMENT BY 1 CACHE 10; 

and a trigger -

CREATE OR REPLACE TRIGGER T_MYTABLE_ID BEFORE INSERT ON MYTABLE REFERENCING NEW AS NEW FOR EACH ROW BEGIN   if(:new.ID is null) then   SELECT S_MYTABLE.nextval   INTO :new.ID   FROM dual;   end if; END; /  ALTER TRIGGER "T_MYTABLE_ID" ENABLE;