Oracle - 修改现有表以自动增加一列

2021-12-30 00:00:00 sql database oracle11g oracle

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

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

相关文章