我在SQL Server上不断收到错误 - 字符串和二进制数据截断，外键约束

Question

我是SQL和SQL Server的新手。我正在尝试实现一个小数据库。但是，我不断收到三个错误。

错误#1：

字符串或二进制数据将被截断

错误#2：

INSERT 语句与外键约束"FK__Formation__Locat__25869641"冲突

错误#3：

INSERT 语句与 FOREIGN KEY 约束"FK__DATA__Classifica__2B3F6F97"冲突。

我尝试了各种不同的更改，但似乎没有任何效果。有人可以向我解释我做错了什么吗？谢谢

CREATE TABLE Location 
(
LocationID CHAR(5) NOT NULL,
LocationName VARCHAR(75) NOT NULL,
PRIMARY KEY (LocationID)    
);
CREATE TABLE Formation 
(
FormationID CHAR(5) NOT NULL,
FormationName VARCHAR(75) NOT NULL,
RockType VARCHAR(75) NOT NULL,
LocationID INT NOT NULL,
PRIMARY KEY (FormationID),
FOREIGN KEY (LocationID) REFERENCES Location(LocationID)    
);
CREATE TABLE Classification 
(
ClassificationID CHAR(5) NOT NULL,
ClassificationType VARCHAR(75) NOT NULL,
ClassificationDescription VARCHAR(75) NOT NULL,
FormationID VARCHAR(5) NOT NULL,
PRIMARY KEY (ClassificationID),
FOREIGN KEY (FormationID) REFERENCES Formation(FormationID)
);
CREATE TABLE DATA 
(
FossilID CHAR(5) NOT NULL,
FossilName VARCHAR(75) NOT NULL,
FossilType VARCHAR(75) NOT NULL,
CatalogueDate DATE NOT NULL,
ClassificationID VARCHAR(5) NOT NULL,
PRIMARY KEY (FossilID),
FOREIGN KEY (ClassificationID) REFERENCES Classification(ClassificationID)
);
INSERT INTO Location 
VALUES ('001', 'Montana');
INSERT INTO Formation 
VALUES ('R01', 'Hell Creek Formation', 'Cretaceous', 001);
INSERT INTO Classification 
VALUES ('001', 'Saurischia', 'Brachiosauridae', 'Hell Creek Formation');
INSERT INTO DATA 
VALUES ('F01', 'Brachiosaurus', 'Vertebrae', '01/MAY/2017', '001');

Answer 1

INT<>VARCHAR(5)<>CHAR(5).列应为相同类型：

CREATE TABLE Location (
LocationID CHAR(5) NOT NULL,
LocationName VARCHAR(75) NOT NULL,
PRIMARY KEY (LocationID)    
);
CREATE TABLE Formation (
FormationID CHAR(5) NOT NULL,
FormationName VARCHAR(75) NOT NULL,
RockType VARCHAR(75) NOT NULL, 
LocationID CHAR(5) NOT NULL,               -- here
PRIMARY KEY (FormationID),
FOREIGN KEY (LocationID) REFERENCES Location(LocationID)    
);
CREATE TABLE Classification (
ClassificationID CHAR(5) NOT NULL,
ClassificationType VARCHAR(75) NOT NULL,
ClassificationDescription VARCHAR(75) NOT NULL,
FormationID CHAR(5) NOT NULL,                 -- here
PRIMARY KEY (ClassificationID),
FOREIGN KEY (FormationID) REFERENCES Formation(FormationID)
);
CREATE TABLE DATA (
FossilID CHAR(5) NOT NULL,
FossilName VARCHAR(75) NOT NULL,
FossilType VARCHAR(75) NOT NULL,
CatalogueDate DATE NOT NULL,
ClassificationID CHAR(5) NOT NULL,
PRIMARY KEY (FossilID),
FOREIGN KEY (ClassificationID) REFERENCES      Classification(ClassificationID)
);

DBFiddleDemo

---

和你的插入语句：

INSERT INTO Location VALUES ('001', 'Montana');
INSERT INTO Formation VALUES ('R01', 'Hell Creek Formation', 'Cretaceous', '001');  --corrected
INSERT INTO Classification VALUES ('001', 'Saurischia', 'Brachiosauridae', 'R01');   --corrected
INSERT INTO DATA VALUES ('F01', 'Brachiosaurus', 'Vertebrae', '01/MAY/2017', '001');

截断错误'Hell Creek Formation'不适合CHAR(5)。您需要参考PK。还可以考虑使用：

INSERT INTO tab(col_name1, col_name2, ...)
VALUES(...)