我创建了两个这样的表:
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