oracle ORA 01400 和 ORA 02296：无法插入空值或将添加的列属性修改为 NOT NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20817280/
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 01400 and ORA 02296 : Cannot insert null or modify added column properties to NOT NULL
提问by GuilhermeMesquitaX
"Modify your query to add a column that subtracts the old salary from the new salary. Label the column Increase. Run the revised query."
“修改您的查询以添加一个从新工资中减去旧工资的列。标记该列增加。运行修改后的查询。”
Well, as per my interpretation, I first attempted to add the column by scripting:
好吧，根据我的解释，我首先尝试通过脚本添加列：
ALTER TABLE EMPLOYEES ADD (
INCREASE2 NUMBER(6));
Then:
然后：
INSERT INTO EMPLOYEES(INCREASE2)
SELECT (salary*1.155) - salary FROM EMPLOYEES;
Error de SQL: ORA-01400: n?o é possível inserir NULL em ("HR"."EMPLOYEES"."EMPLOYEE_ID") 01400. 00000 - "cannot insert NULL into (%s)"
SQL 错误：ORA-01400：n?o é possível inserir NULL em ("HR"."EMPLOYEES"."EMPLOYEE_ID") 01400. 00000 - "不能将 NULL 插入 (%s)"
"HR"."EMPLOYEES"."EMPLOYEE_ID"is the primary key.
"HR"."EMPLOYEES"."EMPLOYEE_ID"是主键。
- I'm not trying to insert a NULL value;
I don't know why oracle isn't accepting my entries. I tried to check whether there was any syntax errors in my expression by performing basic insert:
INSERT INTO EMPLOYEES(INCREASE2) VALUES ('whatever');
- 我不是要插入 NULL 值；
我不知道为什么 oracle 不接受我的条目。我尝试通过执行基本插入来检查我的表达式中是否存在任何语法错误：
INSERT INTO EMPLOYEES(INCREASE2) VALUES ('whatever');
And still I got the error.
我仍然收到错误。
I tried then modifying the column to not null
我尝试然后将该列修改为不为空
ALTER TABLE EMPLOYEES
MODIFY
(INCREASE2 NUMBER(6) NOT NULL);
And:
和：
02296 00000 - "cannot enable (%s.%s) - null values found"
*Cause: an alter table enable constraint failed because the table
contains values that do not satisfy the constraint.
*Action: Obvious
02296 00000 - “无法启用 (%s.%s) - 发现空值”
*原因：更改表启用约束失败，因为表
包含不满足约束的值。
*行动：明显
I found a simple solution for the exercise, but still I am curious about why my code didn't succeed.
我为这个练习找到了一个简单的解决方案，但我仍然很好奇为什么我的代码没有成功。
Resolution:
解析度：
SELECT employee_id, last_name, salary,
ROUND(salary * 1.155, 0) "New Salary",
ROUND(salary * 1.155, 0) - salary "Increase"
FROM employees;
回答by Gordon Linoff
Your code didn't succeed because the column employees.employee_idis a non-null field without a default value. When you run:
您的代码没有成功，因为该列employees.employee_id是一个没有默认值的非空字段。当你运行时：
INSERT INTO EMPLOYEES(INCREASE2)
VALUES ('whatever');
The values of all the other fields in Employeesare assigned the default, or NULLif no default value exists. Because this violates a constraint, you get an error.
中的所有其他字段的值Employees都分配了默认值，或者NULL如果不存在默认值。因为这违反了约束，所以你会得到一个错误。
Normally, a field like employee_idwould be assigned to a sequence. This would automatically insert an auto-incremented value for each new record.
通常，像employee_id这样的字段会被分配给一个序列。这将自动为每条新记录插入一个自动递增的值。
回答by David Aldridge
Wouldn't this be an update, not an insert? Inserting new records makes no sense to me in the context of populating a new column that you have just added.
这不会是更新，而不是插入吗？在填充您刚刚添加的新列的上下文中，插入新记录对我来说毫无意义。
update employees
set increase2 = ROUND(salary * 1.155, 0) - salary;
回答by Wernfried Domscheit
I assume you missunderstood the task: "Modify your queryto add a column that subtracts the old salary from the new salary. Label the column Increase. Run therevised query."
我假设您误解了这个任务：“修改您的查询以添加一个从新工资中减去旧工资的列。标记列增加。运行修改后的查询。”
So your last resolution should be fine, you don't have to alter the actual table.
所以你的最后一个分辨率应该没问题，你不必改变实际的表格。
回答by SHIVANAND RASKONDA
Using NOVALIDATE clause you can overcome this error ORA-02296I am trying to modify table data type NULL to NOT NULL its gives error after using NOVALIDATE the table is altered.see code for more information
使用 NOVALIDATE 子句你可以克服这个错误 ORA-02296我试图将表数据类型 NULL 修改为 NOT NULL 它在使用 NOVALIDATE 后给出错误表被改变。查看代码了解更多信息
alter table emp11 modify eid not null novalidate;
