Oracle 12c:插入到具有标识列的表中

Question

我有一个表，其中有一列类型为Identity column，它也是主键。

CREATE  TABLE identity_demo  (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
description VARCHAR2(100) not null
);

然后我插入几行数据

insert into identity_demo (id, description) values (1,'A');
insert into identity_demo (id, description) values (2,'B');
insert into identity_demo (id, description) values (3,'C');
insert into identity_demo (id, description) values (4,'D');
insert into identity_demo (id, description) values (5,'E');
insert into identity_demo (id, description) values (6,'F');

如果我现在想插入一个没有设置值ID的行，我会得到一个密钥冲突

insert into identity_demo (description) values ('G');

ORA-0001:唯一约束(UWE.IDENTITY_DEMO_PK(verletzt在这里最好的方法是什么？

Answer 1

首先，似乎有一个部分, CONSTRAINT IDENTITY_DEMO_PK PRIMARY KEY (id)

添加到表创建DDL的末尾。

只需删除BY DEFAULT ON NULL部分，即可将身份生成的管理留给DBMS，同时将ID列保留为PRIMARY KEY。在这种情况下，您应该像中一样，从Insert语句中的列列表中删除ID列

INSERT INTO identity_demo (description) VALUES ('G');:

SQL> CREATE  TABLE identity_demo  (
2      id          NUMBER GENERATED AS IDENTITY,
3      description VARCHAR2(100) NOT NULL,
4      CONSTRAINT IDENTITY_DEMO_PK PRIMARY KEY (id)
5    );
Table created
SQL> BEGIN
2    INSERT INTO identity_demo (id, description) VALUES (1,'A');
3    INSERT INTO identity_demo (id, description) VALUES (2,'B');
4    INSERT INTO identity_demo (id, description) VALUES (3,'C');
5    INSERT INTO identity_demo (id, description) VALUES (4,'D');
6    INSERT INTO identity_demo (id, description) VALUES (5,'E');
7    INSERT INTO identity_demo (id, description) VALUES (6,'F');
8  END;
9  /
ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 3
SQL> INSERT INTO identity_demo (description) VALUES ('G');
1 row inserted
SQL> SELECT * FROM identity_demo;
ID DESCRIPTION
---------- -------------------------------------------------------
1 G
SQL> BEGIN
2    INSERT INTO identity_demo (description) VALUES ('A');
3    INSERT INTO identity_demo (description) VALUES ('B');
4    INSERT INTO identity_demo (description) VALUES ('C');
5    INSERT INTO identity_demo (description) VALUES ('D');
6    INSERT INTO identity_demo (description) VALUES ('E');
7    INSERT INTO identity_demo (description) VALUES ('F');
8  END;
9  /
PL/SQL procedure successfully completed
SQL> SELECT * FROM identity_demo;
ID DESCRIPTION
---------- --------------------------------------------------------
1 G
2 A
3 B
4 C
5 D
6 E
7 F
7 rows selected

Answer 2

您手动插入的标识值(1到6(与序列生成器生成的标识值之间发生冲突。您可以通过调整序列生成器来修复它：

ALTER TABLE identity_demo MODIFY (
id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMIT VALUE));

START WITH LIMIT VALUE锁定表，找到最高id，并将内部序列生成器设置为下一个更高的值7。

如果您不想运行此ALTER TABLE命令，可以将数字空间分隔开，例如1…999表示手动值，1000…表示自动值。你必须这样设置你的生成器：

ALTER TABLE identity_demo MODIFY (
id GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1000));
INSERT INTO identity_demo (description) VALUES ('H');
...
6      F
7      G
1000   H