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

User · Answer

You can make auto increment in SQL Modeler  In column properties window Click   General then Tick the box of Auto Increment  After that the auto increment window will be enabled for you

User · Answer

UPDATE  In Oracle 12c onward we have an option to create auto increment field  its better than trigger and sequence    Right click on the table and select  Edit   In  Edit  Table window  select  columns   and then select your PK column  Go to Identity Column tab and select  Generated as Identity  as Type  put 1 in both start with and increment field  This will make this column auto increment     See the below image    From SQL Statement  IDENTITY column is now available on Oracle 12c    create table t1        c1 NUMBER GENERATED by default on null as IDENTITY       c2 VARCHAR2 10            or specify starting and increment values  also preventing any insert into the identity column  GENERATED ALWAYS   again  Oracle 12c  only   create table t1       c1 NUMBER GENERATED ALWAYS as IDENTITY START with 1 INCREMENT by 1       c2 VARCHAR2 10           EDIT   if you face any error like  ORA-30673  column to be modified is not an identity column   then you need to create new column and delete the old one

User · Answer

tom-studee you were right  it s possible to do it in the data modeler   Double click your table  then go to the column section  Here double click on the column which will have the auto increment  In the general section there is a checkbox  autoincrement   just tick it   After that you can also go to the  autoincrement  section to customize it   When you save it and ask the data modeler to generate the SQL script  you will see the sequence and trigger which represent your autoincrement

User · Answer

How to do it with Oracle SQL Developer  In the Left pane  under the connections you will find  Sequences   right click and select create a new sequence from the context sensitive pop up  Fill out the details  Schema name  sequence name  properties start with value  min value  max value  increment value etc   and click ok  Assuming that you have a table with a key that uses this auto increment  while inserting in this table just give  your sequence name nextval  in the field that utilizes this property  I guess this should help

User · Answer

Oracle doesn t have autoincrementing columns  You need a sequence and a trigger  Here s a random blog post that explains how to do it  http   www lifeaftercoffee com 2006 02 17 how-to-create-auto-increment-columns-in-oracle

User · Answer

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

User · Answer

If you want to make your PK auto increment  you need to set the ID column property for that primary key    Right click on the table and select  Edit   In  Edit  Table window  select  columns   and then select your PK column  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

User · Answer

I found this post  which looks a bit old  but I figured I d update everyone on my new findings   I am using Oracle SQL Developer 4 0 2 15 on Windows  Our database is Oracle 10g  version 10 2 0 1  running on Windows   To make a column auto-increment in Oracle -    Open up the database connection in the Connections tab Expand the Tables section  and right click the table that has the column you want to change to auto-increment  and select Edit    Choose the Columns section  and select the column you want to auto-increment  Primary Key column  Next  click the  Identity Column  section below the list of columns  and change type from None to  Column Sequence  Leave the default settings  or change the names of the sequence and trigger if you d prefer  and then click OK   Your id column  primary key  will now auto-increment  but the sequence will be starting at 1  If you need to increment the id to a certain point  you ll have to run a few alter statements against the sequence  This post has some more details and how to overcome this   I found the solution here

[database] how to set auto increment column with sql developer

Examples related to database

Examples related to oracle

Examples related to oracle-sqldeveloper