database 如何使用sql developer设置自动增量列

how to set auto increment column with sql developer

提问by Fendi Tri Cahyono

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.

注意：该图像显示了数据建模器，但问题和最佳答案讨论的是编辑现有数据库。

回答by Daniel Perník

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

如果要使 PK 自动递增，则需要为该主键设置 ID 列属性。

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.

1. 右键单击表格并选择“编辑”。
2. 在“编辑”表窗口中，选择“列”，然后选择您的 PK 列。
3. 转到 ID Column 选项卡并选择 Column Sequence 作为类型。这将创建一个触发器和一个序列，并将序列与主键相关联。

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/

// 我的来源是：http: //techatplay.wordpress.com/2013/11/22/oracle-sql-developer-create-auto-incrementing-primary-key/

回答by Kshitij

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

不幸的是，oracle 不像 mysql 那样支持 auto_increment。你需要付出一些额外的努力才能做到这一点。

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;

回答by user1116119

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.

您可以在 SQL Modeler 中进行自动增量。在列属性窗口中单击：General 然后勾选 Auto Increment 框。之后，将为您启用自动增量窗口。

回答by Bartheleway

@tom-studee you were right, it's possible to do it in the data modeler.

@tom-studee 您说得对，可以在数据建模器中进行。

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.

当您保存它并要求数据建模器生成 SQL 脚本时，您将看到代表您的自动增量的序列和触发器。

回答by Suneel Kumar

UPDATE: In Oracle 12c onward we have an option to create auto increment field, its better than trigger and sequence.

更新：在 Oracle 12c 以后，我们有一个选项来创建自动增量字段，它比触发器和序列更好。

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

• 右键单击表格并选择“编辑”。
• 在“编辑”表窗口中，选择“列”，然后选择您的 PK 列。
• 转到标识列选项卡并选择“作为标识生成”作为类型，在开始和增量字段中输入 1。这将使此列自动增加。

See the below image

见下图

From SQL Statement

从 SQL 语句

IDENTITY column is now available on Oracle 12c:

IDENTITY 列现在在 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)

或指定起始值和增量值，同时防止任何插入标识列（始终生成）（同样，仅适用于 Oracle 12c+）

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.

编辑：如果您遇到任何错误，如“ORA-30673：要修改的列不是标识列”，那么您需要创建新列并删除旧列。

回答by chopsuei3

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.

我在 Windows 上使用 Oracle SQL Developer 4.0.2.15。我们的数据库是在 Windows 上运行的 Oracle 10g（版本 10.2.0.1）。

To make a column auto-increment in Oracle -

要在 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

1. 在连接选项卡中打开数据库连接
2. 展开表部分，右键单击包含要更改为自动递增的列的表，然后选择编辑...
3. 选择 Columns 部分，然后选择要自动递增的列（主键列）
4. 接下来，单击列列表下方的“身份列”部分，并将类型从“无”更改为“列序列”
5. 保留默认设置（或根据需要更改序列和触发器的名称），然后单击“确定”

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 posthas some more details and how to overcome this.

您的 id 列（主键）现在将自动递增，但序列将从 1 开始。如果您需要将 id 递增到某个点，则必须针对该序列运行一些更改语句。
这篇文章有更多细节以及如何克服这个问题。

I found the solution here

我在这里找到了解决方案

回答by Seeder

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

如何使用 Oracle SQL Developer 执行此操作：在左窗格中的连接下，您将找到“序列”，右键单击并从上下文相关的弹出窗口中选择创建新序列。填写详细信息：架构名称、序列名称、属性（以值开头、最小值、最大值、增量值等），然后单击确定。假设您有一个带有使用此 auto_increment 的键的表，而在此表中插入时只需在使用此属性的字段中提供“your_sequence_name.nextval”。我想这应该有帮助！:)

回答by eaolson

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/

Oracle 没有自动递增列。您需要一个序列和一个触发器。这是一个随机的博客文章，解释了如何做到这一点：http: //www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/