Oracle - 修改现有表以自动增加一列 [英] Oracle - Modify an existing table to auto-increment a column

问题描述

我有一个包含以下列的表格:

I have a table with the following column:

NOTEID      NUMBER NOT NULL,

出于所有意图和目的，此列是主键.这个表有几千行，每行都有一个唯一的 ID.之前，应用程序将从表中选择 MAX() 值，添加一个，然后将其用作下一个值.这是一个可怕的解决方案，并且不是事务或线程安全的(事实上，在他们甚至没有对列进行 UNIQUE 约束之前，我可以看到相同的 NOTEID 在 9 种不同的情况下重复).

For all intents and purposes, this column is the primary key. This table has a few thousand rows, each with a unique ID. Before, the application would SELECT the MAX() value from the table, add one, then use that as the next value. This is a horrible solution, and is not transaction or thread safe (in fact, before they didn't even have a UNIQUE constraint on the column and I could see the same NOTEID was duplicated in 9 different occasions)..

我对 Oracle 比较陌生，所以我想知道更改此表并使此列自动递增的最佳语法.如果可能，我想将序列中的下一个值设为表中的 MAX(NOTEID) + 1，或者只是设为 800 或其他值.谢谢！

I'm rather new to Oracle, so I'd like to know the best syntax to ALTER this table and make this column auto-increment instead. If possible, I'd like to make the next value in the sequence be the MAX(NOTEID) + 1 in the table, or just make it 800 or something to start out. Thanks!

推荐答案

如果你的 MAX(noteid) 是 799，那么试试:

If your MAX(noteid) is 799, then try:

CREATE SEQUENCE noteseq
    START WITH 800
    INCREMENT BY 1

然后在插入新记录时，对于 NOTEID 列，您将执行以下操作:

Then when inserting a new record, for the NOTEID column, you would do:

noteseq.nextval

这篇关于Oracle - 修改现有表以自动增加一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！