ORA 01400和ORA 02296:无法插入null或将添加的列属性修改为NOT NULL [英] ORA 01400 and ORA 02296 : Cannot insert null or modify added column properties to NOT NULL

问题描述

修改您的查询，以添加从新工资中减去旧工资的列. 将列标记为增加.运行修改后的查询."

"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));

然后:

INSERT INTO EMPLOYEES(INCREASE2)
SELECT (salary*1.155) - salary FROM EMPLOYEES;

SQL错误:ORA-01400:插入内容无效NULL em("HR"."EMPLOYEES"."EMPLOYEE_ID") 01400. 00000-无法将NULL插入(％s)"

Error de SQL: ORA-01400: não é possível inserir NULL em ("HR"."EMPLOYEES"."EMPLOYEE_ID") 01400. 00000 - "cannot insert NULL into (%s)"

"HR"."EMPLOYEES"."EMPLOYEE_ID"是主键.

1. 我不是要插入NULL值；

2. 我不知道为什么oracle不接受我的输入.我尝试通过执行基本插入来检查表达式中是否存在语法错误:

1. I'm not trying to insert a NULL value;

2. 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');

但我仍然收到错误消息.

And still I got the error.

我尝试过将列修改为不为空

I tried then modifying the column to not null

ALTER TABLE EMPLOYEES
MODIFY
(INCREASE2  NUMBER(6) NOT NULL);

并且:

02296 00000-无法启用(％s.％s)-找到空值"
*原因:更改表启用约束失败，因为该表
包含不满足约束的值.
*动作:明显

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

我为该练习找到了一个简单的解决方案，但我仍然对为什么我的代码无法成功感到好奇.

I found a simple solution for the exercise, but still I am curious about why my code didn't succeed.

解决方案:

SELECT employee_id, last_name, salary,
    ROUND(salary * 1.155, 0) "New Salary",
    ROUND(salary * 1.155, 0) - salary "Increase"
FROM employees;

推荐答案

您的代码未成功，因为列employees.employee_id是非空字段，没有默认值.运行时:

Your code didn't succeed because the column employees.employee_id is a non-null field without a default value. When you run:

INSERT INTO EMPLOYEES(INCREASE2)
    VALUES ('whatever');

为Employees中所有其他字段的值分配默认值，如果没有默认值，则为NULL.因为这违反了约束，所以您会得到一个错误.

The values of all the other fields in Employees are assigned the default, or NULL if no default value exists. Because this violates a constraint, you get an error.

通常，将像employee_id这样的字段分配给序列.这将为每个新记录自动插入一个自动递增的值.

Normally, a field like employee_id would be assigned to a sequence. This would automatically insert an auto-incremented value for each new record.

这篇关于ORA 01400和ORA 02296:无法插入null或将添加的列属性修改为NOT NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！