SQL ORACLE - 无法向非主键插入 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16229004/
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
ORACLE - Cannot insert a NULL value to a NON-Primary Key
提问by Danny Mahoney
I Have searched the web and various forums but I cannot figure out why this won't work. My Database is made up from the following Tables:
我已经搜索了网络和各种论坛，但我无法弄清楚为什么这不起作用。我的数据库由下表组成：
CREATE TABLE CUSTOMER(
custid Number(4),
cfirstname varchar2(30),
csurname varchar2(20) NOT NULL,
billingaddr varchar2(30),
cgender varchar2(1),
CONSTRAINT custpk PRIMARY KEY (custid),
CONSTRAINT genderconst CHECK(cgender in ('M','F','m','f'))
);
CREATE TABLE PRODUCT(
prodid Number(4),
prodname varchar2(30),
currentprice Number(6,2),
CONSTRAINT cprice_chk CHECK(currentprice >= 0 AND currentprice <=5000 ),
CONSTRAINT prodpk PRIMARY KEY (prodid),
CONSTRAINT pricepos CHECK((currentprice >= 0))
);
CREATE TABLE SALESPERSON(
spid Number(4),
spfirstname varchar2(30),
spsurname varchar2(30),
spgender varchar2(1),
CONSTRAINT salespk PRIMARY KEY (spid)
);
CREATE TABLE SHOPORDER(
ordid Number(4),
deliveryaddress varchar2(30),
custid Number(4) NOT NULL,
spid Number(4) NOT NULL,
CONSTRAINT orderpk PRIMARY KEY (ordid),
CONSTRAINT orderfk1 FOREIGN KEY (custid) REFERENCES CUSTOMER(custid),
CONSTRAINT orderfk2 FOREIGN KEY (spid) REFERENCES SALESPERSON(spid)
);
CREATE TABLE ORDERLINE(
qtysold Number(4),
qtydelivered Number(4),
saleprice Number (6,2),
ordid Number(4) NOT NULL,
prodid Number(4) NOT NULL,
CONSTRAINT qty_chk CHECK (qtydelivered >= 0 AND qtydelivered <=99),
CONSTRAINT price_chk CHECK(saleprice >= 0 AND saleprice <=5000 ),
CONSTRAINT linefk1 FOREIGN KEY (ordid) REFERENCES SHOPORDER(ordid),
CONSTRAINT linefk2 FOREIGN KEY (prodid) REFERENCES PRODUCT(prodid)
);
And I am using an insert statement to insert the following:
我正在使用插入语句插入以下内容：
INSERT INTO SHOPORDER(ordid, deliveryaddress, spid)
VALUES (41, NULL, 23);
Whether I use '' or NULL it gives me the error:
无论我使用 '' 还是 NULL 它都会给我错误：
ORA-01400: cannot insert NULL into ("S9710647"."SHOPORDER"."CUSTID");
ORA-01400: 不能将 NULL 插入 ("S9710647"."SHOPORDER"."CUSTID");
My issue that I have not set deliveryaddress as a Primary key nor is it a Foreign key or contain any NOT NULL CoNSTRAINTS.
我的问题是我没有将 deliveryaddress 设置为主键，也不是外键或包含任何 NOT NULL 约束。
Is there a factor that I am missing here? The majority of forums have had people with problems relating to constraints. I cannot see any conflicting constraints.
有没有我在这里遗漏的因素？大多数论坛都有人遇到与约束有关的问题。我看不到任何冲突的约束。
Cheers
干杯
回答by paxdiablo
You're only inserting the columns ordid, deliveryaddressand spidinto SHOPORDERwhich means the others will probably default to NULL.
你只是插入列ordid，deliveryaddress并spid成为SHOPORDER这意味着其他人可能会默认NULL。
However, you've declared custIdas NOT NULLso that's not allowed. You can actually tell what the complaint is by looking at the error message:
但是，您已声明custId为NOT NULL不允许这样做。您实际上可以通过查看错误消息来判断投诉是什么：
ORA-01400: cannot insert NULL into ("S9710647"."SHOPORDER"."CUSTID");
^^^^^^
It's clearly having troubles with the CUSTIDcolumn there and you knowyou haven't explicitly set that, so it must be the default value causing you grief.
CUSTID那里的列显然有问题，并且您知道您没有明确设置它，因此它必须是导致您悲伤的默认值。
You can fix it by either inserting a specific value in to that column as well, or by giving a non-NULL default value to it, though you'll have to ensure the default exists in the CUSTOMERtable lest the orderfk1foreign key constraint will fail.
您可以通过向该列插入特定值或为其提供非 NULL 默认值来修复它，但您必须确保CUSTOMER表中存在默认值，以免orderfk1外键约束失败。
回答by ruakh
The problem is that this:
问题在于：
INSERT INTO SHOPORDER(ordid, deliveryaddress, spid)
VALUES (41, NULL, 23);
uses the default values for all columns that you don't specify an explicit value for, so it's equivalent to this:
对您没有为其指定显式值的所有列使用默认值，因此它等效于：
INSERT INTO SHOPORDER(ordid, deliveryaddress, custid, spid)
VALUES (41, NULL, NULL, 23);
which violates the NOT NULLconstraint on custid.
这违反了NOT NULL上的约束custid。
回答by PM 77-1
CREATE TABLE SHOPORDER(
ordid Number(4),
deliveryaddress varchar2(30),
custid Number(4) NOT NULL,
spid Number(4) NOT NULL,
CONSTRAINT orderpk PRIMARY KEY (ordid),
CONSTRAINT orderfk1 FOREIGN KEY (custid) REFERENCES CUSTOMER(custid),
CONSTRAINT orderfk2 FOREIGN KEY (spid) REFERENCES SALESPERSON(spid)
);
INSERT INTO SHOPORDER(ordid, deliveryaddress, spid)
VALUES (41, NULL, 23);
Your problem is with custidwhich is defines as NOT NULL. You do not specify a valuefor it, hence your attempt to set it to NULL.
您的问题在于custidwhich 定义为NOT NULL。您没有为其指定值，因此您尝试将其设置为NULL.
