CODEWITHSUNDEEP
databaseoracle-databaseoracle-sqldeveloper
Fendi Tri Cahyono
Fendi Tri Cahyono

Reputation: 702

how to set auto increment column with sql developer

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

Oracle SQL Developer

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

Upvotes: 49

Views: 184753

Answers (9)

Adam
Adam

Reputation: 2398

Drag and drop your table from the left side menu into a worksheet and you will get a list of options. Pick "Insert" and then apply, and then done.

Upvotes: 0

Suneel Kumar
Suneel Kumar

Reputation: 5831

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

enter image description here

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.

Upvotes: 4

Daniel Perník
Daniel Perník

Reputation: 5862

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.

enter image description here

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

Upvotes: 100

Bartheleway
Bartheleway

Reputation: 530

@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.

Upvotes: 3

chopsuei3
chopsuei3

Reputation: 240

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 -

  1. Open up the database connection in the Connections tab
  2. Expand the Tables section, and right click the table that has the column you want to change to auto-increment, and select Edit...
  3. Choose the Columns section, and select the column you want to auto-increment (Primary Key column)
  4. Next, click the "Identity Column" section below the list of columns, and change type from None to "Column Sequence"
  5. 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

Upvotes: 2

user1116119
user1116119

Reputation: 91

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.

Upvotes: 6

Seeder
Seeder

Reputation: 113

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! :)

Upvotes: 1

Kshitij
Kshitij

Reputation: 8614

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;

Upvotes: 28

eaolson
eaolson

Reputation: 15094

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/

Upvotes: 1

Related Questions