oracle ORA-32795: 无法插入生成的始终标识列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41275920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-32795: cannot insert into a generated always identity column
提问by ghalib
Guys I am trying to execute below insert statement and I keep getting the error:
伙计们，我试图在插入语句下面执行，但我不断收到错误消息：
cannot insert into a generated always identity column
无法插入生成的始终标识列
the statement is :
声明是：
INSERT INTO leaves_approval
SELECT *
FROM requests_temp r
WHERE r.civil_number = 33322
AND r.request_id = (SELECT Max(s.request_id)
FROM requests_temp s)
采纳答案by Gordon Linoff
What don't you understand about the error? You have an "identity" column, where the value is generated as a sequence. You cannot insert into it. So, list all the other columns:
你有什么不明白的错误？您有一个“身份”列，其中的值是作为序列生成的。你不能插入它。因此，列出所有其他列：
INSERT INTO LEAVES_APPROVAL(col1, col2, col3, . . .)
SELECT col1, col2, col3, . . .
FROM REQUESTS_TEMP r
WHERE r.CIVIL_NUMBER = 33322 AND
r.REQUEST_ID = (SELECT MAX(s.REQUEST_ID) FROM REQUESTS_TEMP s);
In general, it is a good idea to list all the columns in an INSERTanyway. This prevents unexpected errors, because the columns are in the wrong order or the tables have different numbers of columns.
通常，以任何方式列出所有列是个好主意INSERT。这可以防止意外错误，因为列的顺序错误或表的列数不同。
回答by David ???? Markovitz
One of the columns in your target table (leaves_approval) contains an identity column that was defined as Generated always.
Identity columns can be created in 2 modes - Generated always, that cannotbe assigned and Generated by defaultthat canbe assigned.
目标表 (leaves_approval) 中的一列包含定义为Generated always的标识列。
-标识列可在两种模式中创建始终产生，即不能进行分配，由默认生成的是可以进行分配。
If you wish you can change the column mode and then do your insert "as is".
Take in consideration that this might create duplicates in the identity column or failed due to constraints.
如果您愿意，可以更改列模式，然后“按原样”插入。
考虑到这可能会在标识列中创建重复项或由于约束而失败。
ALTER TABLE leaves_approval MODIFY **my_identity_column** GENERATED BY DEFAULT AS IDENTITY;
Or you can exclude the identity column from the INSERT list (but you'll have to indicate the full column list, except for the identity column), e.g. -
或者您可以从 INSERT 列表中排除标识列（但您必须指出完整的列列表，标识列除外），例如 -
INSERT INTO leaves_approval (c1,c2,c3,c4,...)
SELECT c1,c2,c3,c4 ...
FROM requests_temp r
WHERE r.civil_number = 33322
AND r.request_id = (SELECT Max(s.request_id)
FROM requests_temp s)
Database SQL Language Reference - CREATE TABLE
ALWAYS If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned. This is the default.
BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column. If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.
ALWAYS 如果指定 ALWAYS，则 Oracle 数据库始终使用序列生成器为列分配值。如果您尝试使用 INSERT 或 UPDATE 为列显式分配值，则将返回错误。这是默认设置。
BY DEFAULT 如果指定 BY DEFAULT，则 Oracle 数据库默认使用序列生成器为列赋值，但您也可以为列显式赋值。如果您指定 ON NULL，那么当后续的 INSERT 语句尝试分配计算结果为 NULL 的值时，Oracle 数据库使用序列生成器为该列分配一个值。
回答by senthil kumar raju
Example: my_table_column NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY - if you have the column defined as, then it will get the value when it is NULL and will not interfere if you are to insert/update with values for that column. It worked for me.
示例：my_table_column NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY - 如果您将列定义为，那么它将在为 NULL 时获取值，并且如果您要插入/更新该列的值，则不会干扰。它对我有用。
