SQL/Oracle-“无法将空值插入主键”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32624386/
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
SQL/Oracle - "Cannot insert null into primary key"
提问by Ivar Reukers
So, I have an Oracle database (in APEX) in which I have a column called "Scheme".
所以，我有一个 Oracle 数据库（在 APEX 中），其中有一个名为“方案”的列。
Like seen below:
如下图所示：
CREATE TABLE Scheme
(
Scheme_ID NUMBER NOT Null,
description VARCHAR2 (800) ,
price FLOAT (3) ,
amount_sold INTEGER ,
Personal_Trainer_ID NUMBER NOT NULL
) ;
ALTER TABLE Schema ADD CONSTRAINT Schema_PK PRIMARY KEY ( Schema_ID ) ;
Now, all my tables are set-up like this and perfectly working, but when I try an insert on my Scheme, it says I'm trying to insert null into the primary key scheme_ID.
现在，我所有的表都像这样设置并且运行良好，但是当我尝试在我的 Scheme 上插入时，它说我正在尝试将 null 插入到主键 scheme_ID 中。
I'll show you 2 SQL inserts I use. One for a personal_trainer, and one for the Scheme.
我将向您展示我使用的 2 个 SQL 插入。一种用于personal_trainer，一种用于Scheme。
INSERT INTO Personal_Trainer (name, loginname, date_of_birth, password)
VALUES('Bojan', 'Bojan', '15-07-1974','fitline');
Peronal_Trainer has a Personal_Trainer_ID as primary key, exactly set-up like the Scheme. Inserting this command works perfectly fine.
Peronal_Trainer 有一个 Personal_Trainer_ID 作为主键，与 Scheme 完全一样。插入这个命令工作得很好。
insert into schema (description, price, amount_sold, Personal_Trainer_ID)
values ('3x pushups, 5x bench, 7x squats - 15kg',200, 1, 2);
Now, when I try to insert this command I get this error message:
现在，当我尝试插入此命令时，我收到此错误消息：
ORA-01400: cannot insert NULL into ("SCHEME"."SCHEME_ID")
ORA-01400: 无法将 NULL 插入 ("SCHEME"."SCHEME_ID")
****************************EDIT*****************************************
****************************编辑********************* ********************
CREATE TABLE Personal_Trainer
(
Personal_Trainer_ID NUMBER NOT NULL,
name VARCHAR2 (35) ,
date_of_birth DATE ,
loginname VARCHAR2 (35) ,
password VARCHAR2 (35)
) ;
ALTER TABLE Personal_Trainer ADD CONSTRAINT Personal_Trainer_PK PRIMARY KEY ( Personal_Trainer_ID ) ;
This is my table of Personal Trainer.
这是我的私人教练表。
采纳答案by Ivar Reukers
Oh I found it, the NOT NULL constraint still works fine, but my Column is actually called 'Schema' (other language) and apparently this has a definition in Oracle. So it's never altered as a primary key. Changing the table his name should work.
哦，我发现了，NOT NULL 约束仍然可以正常工作，但我的列实际上被称为“架构”（其他语言），显然这在 Oracle 中有一个定义。所以它永远不会被改变为主键。换表他的名字应该有效。
Thanks for the fast responds and help!
感谢您的快速响应和帮助！
DONT USE RESERVED TERMS FOR YOUR TABLE NAMES!
不要为您的表名使用保留的术语！
回答by Mureinik
You need to provide a unique, not null, value for schemeid.
您需要为 提供唯一的而非空的值schemeid。
Oracle 12c allows you to elegantly define this column as an identity column:
Oracle 12c 允许您优雅地将此列定义为标识列：
CREATE TABLE Scheme
(
Scheme_ID NUMBER GENERATED BY DEFAULT AS IDENTITY
description VARCHAR2 (800) ,
price FLOAT (3) ,
amount_sold INTEGER ,
Personal_Trainer_ID NUMBER NOT NULL
);
In earlier Oracle versions this option isn't available, unfortunately. The idiomatic solution would be to declare a sequence:
不幸的是，在早期的 Oracle 版本中，此选项不可用。惯用的解决方案是声明一个序列：
CREATE SEQUENCE scheme_id_seq;
And either use it directly:
或者直接使用它：
INSERT INTO schema
(scheme_id, description, price, amount_sold, Personal_Trainer_ID)
VALUES
(scheme_id_seq.nextval, '3x pushups, 5x bench, 7x squats - 15kg',200, 1, 2);
Or create a trigger to fill it in automatically:
或者创建一个触发器来自动填充它：
CREATE OR REPLACE TRIGGER schema_insert_tr
BEFORE INSERT ON schema
FOR EACH ROW
BEGIN
IF :new.scheme_id IS NULL THEN
SELECT scheme_id_seq.nextval INTO :new.scheme_id FROM DUAL;
END IF;
END;
回答by Moudiz
because Scheme_IDis primary key and not null,
因为Scheme_ID是主键而不是空，
to solve it everytime you insert a record you should insert value for Scheme_IDor remove it from pk and make it null
要在每次插入记录时解决它，您应该为Scheme_IDpk插入值或将其从 pk 中删除并使其为空
As for personal_trainer, are you sure personal_trainer_id is primary key ? because its impossible to insert null into primary key
至于personal_trainer，你确定personal_trainer_id 是主键吗？因为不可能在主键中插入空值
回答by Debojit Das
Primary key has not null constraint, therefore you have to insert some value in the primary key for the insertion to work.
主键没有空约束，因此您必须在主键中插入一些值才能使插入工作。
回答by Anton Zaviriukhin
Oracle don't have auto_increment columns.
Instead you need to create sequence likeCREATE SEQUENCE SEQ_SCHEME_ID
and then useinsert into schema (Scheme_ID,description, price, amount_sold, Personal_Trainer_ID)
values (SEQ_SCHEME_ID.nextval,'3x pushups, 5x bench, 7x squats - 15kg',200, 1, 2);
Oracle 没有 auto_increment 列。
相反，您需要创建序列CREATE SEQUENCE SEQ_SCHEME_ID
，然后使用insert into schema (Scheme_ID,description, price, amount_sold, Personal_Trainer_ID)
values (SEQ_SCHEME_ID.nextval,'3x pushups, 5x bench, 7x squats - 15kg',200, 1, 2);
You may put sequence logic in "before insert for each row" trigger.
In such case you may insert row without mentioning primary key column.
It will be filled in with the sequence.nextval value automatically by trigger.
This is the case of Personal_Trainer table.
You may find this trigger and create similar one for SCHEMA table.
您可以将序列逻辑放在“每行插入之前”触发器中。
在这种情况下，您可以插入行而不提及主键列。
触发器会自动填充sequence.nextval 值。
这是 Personal_Trainer 表的情况。
您可能会找到此触发器并为 SCHEMA 表创建类似的触发器。
