sql-server - 无法将值 NULL 插入列不允许空值
问题描述
我创建了两个这样的表：
CREATE TABLE Seller_(
ID_SELLER INT NOT NULL,
PASS VARCHAR (8) NULL,
FIO VARCHAR (30) NULL,
ADRESS VARCHAR (30) NULL,
PRIMARY KEY (ID_SELLER),
);
CREATE TABLE Zakaz_(
ID_ZAKAZ INT NOT NULL,
DATE_ DATE NULL,
ID_SELLER INT NOT NULL FOREIGN KEY REFERENCES Seller_(ID_SELLER)
PRIMARY KEY (ID_ZAKAZ)
);
我像这样插入这些表：
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (111,'abcd1','Anton','derevnya universiady');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (112,'abcd2','Andrey','pushkina');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (113,'abcd3','Abdul','krasnaya pozitsiya');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (114,'abcd1','Alsu','derevnya universiady');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (115,'abcd2','Anna','pushkina');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (116,'abcd3','Azamat','krasnaya pozitsiya');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (117,'abcd2','Sergei','pushkina');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (118,'abcd1','Leysan','derevnya universiady');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (119,'abcd1','Katya','derevnya universiady');
INSERT INTO Seller_(ID_SELLER,PASS,FIO,ADRESS) VALUES (120,'abcd3','Anastasia','krasnaya pozitsiya');
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (111,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (112,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (113,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (114,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (115,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (116,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (117,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (118,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (119,GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ,DATE_) VALUES (112,GETDATE());
我收到此错误：
Msg 515, Level 16, State 2, Line 27
Cannot insert the value NULL into column 'ID_SELLER', table 'test.dbo.Zakaz_'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Line 28
Cannot insert the value NULL into column 'ID_SELLER', table 'test.dbo.Zakaz_'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Line 29
Cannot insert the value NULL into column 'ID_SELLER', table 'test.dbo.Zakaz_'; column does not allow nulls. INSERT fails.
...
为什么我会收到此错误，我该怎么办？
解决方案
Zakaz_ 表中的 ID_SELLER 列必须包含 Seller_ 表中记录的 ID。这样您就可以知道每个 Zakaz_ 与哪个 Seller_ 相关联。
例如，这会起作用，尽管只有您可以确定正确的值：
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (111, 'abcd1', 'Anton', 'derevnya universiady');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (112, 'abcd2', 'Andrey', 'pushkina');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (113, 'abcd3', 'Abdul', 'krasnaya pozitsiya');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (114, 'abcd1', 'Alsu', 'derevnya universiady');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (115, 'abcd2', 'Anna', 'pushkina');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (116, 'abcd3', 'Azamat', 'krasnaya pozitsiya');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (117, 'abcd2', 'Sergei', 'pushkina');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (118, 'abcd1', 'Leysan', 'derevnya universiady');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (119, 'abcd1', 'Katya', 'derevnya universiady');
INSERT INTO Seller_(ID_SELLER, PASS, FIO, ADRESS) VALUES (120, 'abcd3', 'Anastasia', 'krasnaya pozitsiya');
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (201, 111, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (202, 112, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (203, 113, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (204, 114, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (205, 115, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (206, 116, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (207, 117, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (208, 118, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (209, 119, GETDATE());
INSERT INTO Zakaz_(ID_ZAKAZ, ID_SELLER, DATE_) VALUES (210, 112, GETDATE());
查看我在两个表中的 ID_SELLER 值（111、112、...）中使用相同 ID 的方式，以便在它们之间建立关系。
查看结果：
SELECT * FROM Seller_;
ID_SELLER PASS FIO ADRESS
----------- -------- ------------------------------ ------------------------------
111 abcd1 Anton derevnya universiady
112 abcd2 Andrey pushkina
113 abcd3 Abdul krasnaya pozitsiya
114 abcd1 Alsu derevnya universiady
115 abcd2 Anna pushkina
116 abcd3 Azamat krasnaya pozitsiya
117 abcd2 Sergei pushkina
118 abcd1 Leysan derevnya universiady
119 abcd1 Katya derevnya universiady
120 abcd3 Anastasia krasnaya pozitsiya
SELECT * FROM Zakaz_;
ID_ZAKAZ DATE_ ID_SELLER
----------- ---------- -----------
201 2019-12-19 111
202 2019-12-19 112
203 2019-12-19 113
204 2019-12-19 114
205 2019-12-19 115
206 2019-12-19 116
207 2019-12-19 117
208 2019-12-19 118
209 2019-12-19 119
210 2019-12-19 112
您现在甚至可以加入表格：
SELECT *
FROM Seller_ s
INNER JOIN Zakaz_ z
on s.ID_SELLER = z.ID_SELLER;
ID_SELLER PASS FIO ADRESS ID_ZAKAZ DATE_ ID_SELLER
----------- -------- ------------------------------ ------------------------------ ----------- ---------- -----------
111 abcd1 Anton derevnya universiady 201 2019-12-19 111
112 abcd2 Andrey pushkina 202 2019-12-19 112
113 abcd3 Abdul krasnaya pozitsiya 203 2019-12-19 113
114 abcd1 Alsu derevnya universiady 204 2019-12-19 114
115 abcd2 Anna pushkina 205 2019-12-19 115
116 abcd3 Azamat krasnaya pozitsiya 206 2019-12-19 116
117 abcd2 Sergei pushkina 207 2019-12-19 117
118 abcd1 Leysan derevnya universiady 208 2019-12-19 118
119 abcd1 Katya derevnya universiady 209 2019-12-19 119
112 abcd2 Andrey pushkina 210 2019-12-19 112
推荐阅读
- python - 如何正确使用 Marshmallow 在 Mysql 上插入新数据？
- openmp - 将 Eigen::BICGSTAB 与具有不完整 ILU 的 OpenMP 结合使用不会并行运行
- mongodb - MongoDB：分片集合中的 $in 运算符
- javascript - 从 React 中检索状态数据
- reactjs - 如何在反应路由器 v5 中监听路由变化？
- kubernetes - 什么是 kubernetes 守护程序集中的“可用”列
- java - Axon Framewok vs GDPR（从域事件条目表中删除物理个人数据）
- python - 取两个列同时增加的行
- c# - 为什么 Office 365 的 SMTP 身份验证在一个域中失败，而在另一个域中失败？
- ios - AADB2C 使用 Apple 登录并使用 Apple 私人电子邮件中继服务