Oracle / PostgreSQL中的序列在插入语句中没有ID [英] Sequence in Oracle/PostgreSQL with no ID in insert statement

问题描述

我尝试使用巧妙的序列生成器创建表以使用此插入结构：

I'm try to create table with clever sequence generator for using this insert-strucure:

insert into SOMEUSERS (SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
values ('Artem', 'PracTimPatie');

代替此：

insert into SOMEUSERS (SOMEUSERS_ID, SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
values (2, 'Artem', 'PracTimPatie');

或以下结构：

insert into SOMEUSERS (SOMEUSERS_ID, SOMEUSERS_NAME, SOMEUSERS_PASSWORD) 
values (GEN_ID_SOMEUSERS.nextval, 'Artem', 'PracTimPatie');

当我执行以下SQL脚本时：

When I executing the following sql script:

create sequence gen_id_someUsers START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE TABLE loc_db.someUsers
( someUsers_id number(10) DEFAULT gen_id_someUsers.NEXTVAL NOT NULL, --because of this row
  someUsers_name varchar2(50) NOT NULL,
  someUsers_password varchar2(50),
  CONSTRAINT someUsers_pk PRIMARY KEY (someUsers_id)
);

以下通知已发给我：

错误报告-SQL错误：ORA-00984：此处不允许使用列
00984。00000-此处不允许使用列

Error report - SQL Error: ORA-00984: column not allowed here 00984. 00000 - "column not allowed here"

为清楚起见，在这种情况下说：

For clarity, said that in this case:

    ...
CREATE TABLE loc_db.someUsers
( someUsers_id number(10) NOT NULL, --correct this row
...

序列GEN_ID_SOMEUSERS已创建。

Sequence GEN_ID_SOMEUSERS created.

表LOC_DB.SOMEUSERS已创建。

Table LOC_DB.SOMEUSERS created.

如何配置舒适的序列生成器？

（如果也使用 PostgreSQL 。如果没有触发（尽可能容易）

How can I configure comfortable sequence generator?

(in case of PostgreSQL too. If possible with no trigger(as easily as possible)

推荐答案

Oracle 12c引入了身份列：

Oracle 12c introduces Identity columns:

CREATE TABLE SOMEUSERS (
  SOMEUSERS_ID       NUMBER(10) GENERATED ALWAYS AS IDENTITY
                     CONSTRAINT SOMEUSERS__SOMEUSERS_ID__PK PRIMARY KEY,
  SOMEUSERS_NAME     VARCHAR2(50)
                     CONSTRAINT SOMEUSERS__SOMEUSERS_NAME__NN NOT NULL,
  SOMEUSERS_PASSWORD VARCHAR2(50)
);

如果要在早期版本中使用，则需要一个触发器和一个序列：

If you want to do it in earlier versions then you will need a trigger and a sequence:

CREATE TABLE SOMEUSERS (
  SOMEUSERS_ID       NUMBER(10)
                     CONSTRAINT SOMEUSERS__SOMEUSERS_ID__PK PRIMARY KEY,
  SOMEUSERS_NAME     VARCHAR2(50)
                     CONSTRAINT SOMEUSERS__SOMEUSERS_NAME__NN NOT NULL,
  SOMEUSERS_PASSWORD VARCHAR2(50)
);
/

CREATE SEQUENCE gen_id_someUsers START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
/

CREATE OR REPLACE TRIGGER SOMEUSERS__ID__TRG 
BEFORE INSERT ON SOMEUSERS
FOR EACH ROW
BEGIN
  :new.SOMEUSERS_ID := gen_id_someUsers.NEXTVAL;
END;
/

然后您就可以这样做（将Identity列或触发器与您的序列）：

You can then just do (either with the identity column or the trigger combined with your sequence):

INSERT INTO SOMEUSERS (
  SOMEUSERS_NAME,
  SOMEUSERS_PASSWORD
) VALUES (
  'Name',
  'Password'
);

这篇关于Oracle / PostgreSQL中的序列在插入语句中没有ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！