Oracle как сделать автоинкремент

Обновлено: 04.07.2024

Я знаю, что в Oracle нет возможности добавить для поля autoincrement , но знаю один способ, как это сымитировать.

Кто еще знает? Чем больше вариантов, тем интереснее.

Вот тот, что я знаю:

50.4k 153 153 золотых знака 54 54 серебряных знака 211 211 бронзовых знаков

Однозначно только сиквенс, вариант с max 100% вызовет проблему при работе нескольких пользователей. не посчитайте "троллем" но max не будет использовать ни один человек который хотя бы сколько то проработал с Oracle

Начиная с 12c (12.1.0.1 06/2013) появилась identity column (=auto-increment):

До этого, кроме широко известного решения с генераторами последовательностей (sequences), можно было в качестве сурогатных ключей использовать UUID:

, что давало экономию места 8 байт (

33%) на само поле и на индекс по сравнению с NUMBER .

50.4k 153 153 золотых знака 54 54 серебряных знака 211 211 бронзовых знаков

Иногда интересно решение со строго возрастающей последовательностью, которая видна и уникальна только для текущей сессии. Например, подготовка данных для вставки в целевые таблицы, в которых используется другое решение для обеспечения уникальности.

С переменными сессии это реализуется очень просто:

Конечно, использовать имеет смысл только во временных таблицах:

50.4k 153 153 золотых знака 54 54 серебряных знака 211 211 бронзовых знаков

Вариант с сиквенсом самый лучший, потому что нативный.

Вариант с max() - медленный и не обеспечивает уникальности сгенерённого ключа.

Вариант с триггером - будут потери на переключение контекста между sql машиной и pl/sql машиной. Кроме того триггер будет срабатывать при загрузке данных sql-loader-ом или любой другой вставке с уже существующим идшником.

Проведите эксперимент. 1 сессия. select max() + 1, а потом insert и НЕ КОММИТЕТЬ. 2 сессия - select max()+1. Что получите? Правильно. В двух сессиях одинаковые ключи. Ну и кроме того select - это запрос к таблице, а взятие nextval - это обращение к кэшу значений сиквенса. Да ежу понятно, что с сиквенсом самый лучший вариант. Человек попросил побольше вариантов или вы читаете вопрос слово через слово? :)

UP1: исправил запросы

Кавабунга))) я думал тут все профи на Oracle, и что даже не смотрят такие "простые вопросы", СПАСИБО, не дал теме загнуться, буду сидеть теперь и наслаждаться твоими вкусными примерчики))))))

MS SQL и другие базы используют очень удобный способ автоникремента. Чаще всего его применяют для ключевых полей таблицы. Первоначально в Oracle не было автоинкремента, но позднее в версии 12 (выпуск 2015 года), автоинкремент появился. Вот статья, котрая описывает оба подхода.

Я расскажу, как можно использовать sequence для генерации нового значения ключа. Этот хороший способ для демонстрации того, что представляет собой структура sequnce, и как создавать простые триггеры. При вставке нового значения (оператор insert) можно использовать построчный триггер на таблице (before insert for each row). В примере ниже таблица называется demo, ключевое поле demo.id. Тип этого поля можно выбрать произвольно (integer, varchar2 и т.д.). Обычно выбирают тип integer. Поскольку поле является ключом, для него добавляют ограничение уникальности и ограничение not null.

Здесь sq_demo_id - счетчик, sequence c автоматическим увеличением на единицу при каждом вызове. Если нужно будет узнать текущее значение счетчика, то используйте sq_demo_id.curval. Создание тригера является однократным действием. Не нужно заботиться о конфликтах с параллелными сеансами, эта проблема устраняется в триггере. Новый подход, создание счетчика через оператор identity ещё больше облегчает жизнь программистам.

При его реализации похожие структуры создаются автоматически, самим ядром PL/SQL. Они обладают меньшей гибкостью в настройках, но код становится легче, его проще поддерживать.

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.

How can I create a column that behaves like auto increment in Oracle 11g?

36.5k 5 5 gold badges 45 45 silver badges 68 68 bronze badges

6,735 15 15 gold badges 35 35 silver badges 65 65 bronze badges You can create a BEFORE INSERT trigger on the table and pull values out of a sequence to create auto-increment

17 Answers 17

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

UPDATE:

IDENTITY column is now available on Oracle 12c:

or specify starting and increment values, also preventing any insert into the identity column ( GENERATED ALWAYS ) (again, Oracle 12c+ only)

Alternatively, Oracle 12 also allows to use a sequence as a default value:

468k 84 84 gold badges 723 723 silver badges 794 794 bronze badges 10.1k 1 1 gold badge 26 26 silver badges 49 49 bronze badges CREATE SEQUENCE dept_seq; creates dept_seq . like a table .. but in this case its only a number that you can increase with dept_seq.NEXTVAL . see the trigger. As was mentioned, the original code would fail when encountering a line with ID specified. But how about this case: The trigger would assign the id (automatically) only if there was no id specified explicitly in INSERT. This would fail, right? And what is the proper way to do this? You do not need to use SELECT .. INTO in the trigger you can just do :new.id := dept_seq.NEXTVAL; .

SYS_GUID returns a GUID-- a globally unique ID. A SYS_GUID is a RAW(16) . It does not generate an incrementing numeric value.

If you want to create an incrementing numeric key, you'll want to create a sequence.

You would then either use that sequence in your INSERT statement

Or you can define a trigger that automatically populates the primary key value using the sequence

If you are using Oracle 11.1 or later, you can simplify the trigger a bit

If you really want to use SYS_GUID

217k 22 22 gold badges 347 347 silver badges 371 371 bronze badges What does CACHE 100; in CREATE SEQUENCE name_of_sequence START WITH 1 INCREMENT BY 1 CACHE 100; do? @turbanoff - Good catch. Updated my answer. The SYS_GUID documentation declares a raw(32) which confused me. @JustinCave I have used your approach in creating the sequence and trigger. What if I delete a row programmatically (java), will the ID(primary key) gets adjusted as well?

In Oracle 12c onward you could do something like,

And in Oracle (Pre 12c).

32.9k 6 6 gold badges 66 66 silver badges 121 121 bronze badges

5,010 15 15 gold badges 58 58 silver badges 80 80 bronze badges @JonHeller I personally say the IDENTITY example is much clearer in this answer. The WHEN (new.MAP_ID IS NULL) is not in the accepted answer. Upvoted. @dcsohl, WHEN ( new.MAP_ID is null) is not a good code in this case and is already explained in the comment section by @A.B.Cade under accepted answer.. have a read;) I got [42000][907] ORA-00907: missing right parenthesis when running the version for Oracle 12c onward. Any idea ?

Here are three flavors:

numeric. Simple increasing numeric value, e.g. 1,2,3.
GUID. globally univeral identifier, as a RAW datatype.
GUID (string). Same as above, but as a string which might be easier to handle in some languages.

x is the identity column. Substitute FOO with your table name in each of the examples.

Oracle 12c introduces these two variants that don't depend on triggers:

The first one uses a sequence in the traditional way; the second manages the value internally.

276k 117 117 gold badges 315 315 silver badges 442 442 bronze badges

Assuming you mean a column like the SQL Server identity column?

In Oracle, you use a SEQUENCE to achieve the same functionality. I'll see if I can find a good link and post it here.

26.5k 19 19 gold badges 78 78 silver badges 139 139 bronze badges

Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column. In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger. From 12c onward, you can create your own Table and define the column that has to be generated as an Identity.

The following article explains how to use it:

3,441 8 8 gold badges 38 38 silver badges 55 55 bronze badges

829 1 1 gold badge 14 14 silver badges 17 17 bronze badges While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

Trigger and Sequence can be used when you want serialized number that anyone can easily read/remember/understand. But if you don't want to manage ID Column (like emp_id) by this way, and value of this column is not much considerable, you can use SYS_GUID() at Table Creation to get Auto Increment like this.

Now your emp_id column will accept "globally unique identifier value". you can insert value in table by ignoring emp_id column like this.

So, it will insert unique value to your emp_id Column.

What happens when a row is deleted? Will the SYS_GUID() its id values as well?

Starting with Oracle 12c there is support for Identity columns in one of two ways:

Sequence + Table - In this solution you still create a sequence as you normally would, then you use the following DDL:

CREATE TABLE MyTable (ID NUMBER DEFAULT MyTable_Seq.NEXTVAL, . )

Table Only - In this solution no sequence is explicitly specified. You would use the following DDL:

CREATE TABLE MyTable (ID NUMBER GENERATED AS IDENTITY, . )

If you use the first way it is backward compatible with the existing way of doing things. The second is a little more straightforward and is more inline with the rest of the RDMS systems out there.

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.

4,032 2 2 gold badges 31 31 silver badges 38 38 bronze badges 662 1 1 gold badge 9 9 silver badges 19 19 bronze badges

8 Answers 8

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.

4,558 2 2 gold badges 33 33 silver badges 40 40 bronze badges Great answer. However, may I amend that it is important to take a look at the checkbox for "Check column is null before inserting" which might not be want you want. When using a sequence ID, you would usually not want it to be populated from elsewhere, (May depend, but be sure to think for a moment.) Wonderful answer. Thank you . Count an upvote from me too :)

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 -

You will need to create a sequence -

8,118 2 2 gold badges 22 22 silver badges 34 34 bronze badges

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.

37.9k 20 20 gold badges 91 91 silver badges 119 119 bronze badges Thanks for your post! Please do not use signatures/taglines in your posts. Your user box counts as your signature, and you can use your profile to post any information about yourself you like. FAQ on signatures/taglines

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

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:

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

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.

5,067 2 2 gold badges 28 28 silver badges 40 40 bronze badges

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.

Как настроить автоматическое увеличение столбца с помощью Oracle SQL Developer? Почему форма отключена?

Примечание: На рисунке показан Data Modeler, но вопрос и топ-ответ говорят о редактировании существующей базы данных.

Если вы хотите сделать автоинкремент PK, вам нужно установить свойство столбца ID для этого первичного ключа.

Щелкните правой кнопкой мыши по столу и выберите "Изменить".
В окне "Редактировать" таблицы выберите "столбцы", а затем выберите столбец PK.
Перейдите на вкладку "Столбец идентификатора" и выберите "Тип столбца". Это создаст триггер и последовательность и свяжет последовательность с первичным ключом.

Смотрите картинку ниже для лучшего понимания.

К сожалению, Oracle не поддерживает auto_increment, как MySQL. Вам нужно приложить немного дополнительных усилий, чтобы получить это.

скажи это твой стол -

Вам нужно будет создать последовательность -

Вы можете сделать автоинкремент в SQL Modeler. В окне свойств столбца нажмите: Общие, затем установите флажок Автоинкремент. После этого окно автоматического увеличения будет включено для вас.

@ tom-studee, ты был прав, это можно сделать в моделере данных.

Дважды щелкните по таблице, затем перейдите в раздел столбцов. Здесь дважды щелкните по столбцу, который будет иметь автоинкремент. В общем разделе есть флажок "автоинкремент", просто отметьте его.

После этого вы также можете перейти в раздел "Автоинкремент", чтобы настроить его.

Когда вы сохраните его и попросите, чтобы разработчик данных сгенерировал скрипт SQL, вы увидите последовательность и триггер, которые представляют ваш автоинкремент.

] : в Oracle 12c и более поздних версиях у нас есть возможность создать поле автоматического увеличения, которое лучше, чем триггер и последовательность ,.

Щелкните правой кнопкой мыши по столу и выберите "Изменить".
В окне "Редактировать" таблицы выберите "столбцы", а затем выберите столбец PK.
Перейдите на вкладку "Столбец идентичности" и выберите "Сгенерировано как удостоверение" в качестве Типа, поставьте 1 в поле начала и увеличения. Это сделает этот столбец автоинкрементным.

См. Изображение ниже

Из оператора SQL

Столбец IDENTITY теперь доступен в Oracle 12c:

или укажите начальное значение и значение приращения, также предотвращая любую вставку в столбец идентификаторов (GENERATED ALWAYS) (опять же, только Oracle 12c +)

] : если вы столкнулись с какой-либо ошибкой типа "ORA-30673: изменяемый столбец не является столбцом идентификаторов ", тогда вам нужно создать новый столбец и удалить старый.

Как это сделать с Oracle SQL Developer: На левой панели под соединениями вы найдете "Последовательности", щелкните правой кнопкой мыши и выберите "Создать новую последовательность" из контекстно-зависимого всплывающего окна. Заполните данные: имя схемы, имя_последовательности, свойства (начните со значения, минимального значения, максимального значения, значения приращения и т.д.) И нажмите кнопку ОК. Предполагая, что у вас есть таблица с ключом, который использует этот auto_increment, при вставке в эту таблицу просто укажите "your_sequence_name.nextval" в поле, которое использует это свойство. Я думаю, это должно помочь! :)

Я нашел этот пост, который выглядит немного старым, но я решил, что буду сообщать всем о своих новых находках.

Я использую Oracle SQL Developer 4.0.2.15 в Windows. Наша база данных - Oracle 10g (версия 10.2.0.1), работающая в Windows.

Чтобы сделать автоинкремент столбца в Oracle -

Откройте соединение с базой данных на вкладке Соединения
Разверните раздел "Таблицы", щелкните правой кнопкой мыши таблицу, в которой есть столбец, который вы хотите изменить на автоинкремент, и выберите "Редактировать . ".
Выберите раздел "Столбцы" и выберите столбец, который вы хотите автоматически увеличить (столбец "Первичный ключ")
Затем щелкните раздел "Столбец идентификации" под списком столбцов и измените тип с "Нет" на "Последовательность столбцов".
Оставьте настройки по умолчанию (или измените имена последовательности и триггера, если хотите) и затем нажмите OK

Ваш столбец идентификатора (первичный ключ) теперь будет автоматически увеличиваться, но последовательность будет начинаться с 1. Если вам нужно увеличить идентификатор до определенной точки, вам придется выполнить несколько операторов alter для этой последовательности.
Этот пост есть некоторые подробности и как это преодолеть.

Читайте также: