sql - 尝试正确插入数据

Question

在收到模糊 Lollipop 的非常好的修正后，我修改了我的代码，为每一行数据创建了一个插入语句。这是我输入的代码:

INSERT DEPARTMENTS
(Department_Id,Department_Name,Manager_Id,Location_Id)
VALUES
('D0001','Think Tank',NULL,'L0001')
GO

INSERT DEPARTMENTS
(Department_Id,Department_Name,Manager_Id,Location_Id)
VALUES
('D0002','Creators',NULL,'L0002')
GO

INSERT DEPARTMENTS
(Department_Id,Department_Name,Manager_Id,Location_Id)
VALUES
('D0003','Marketers',NULL,'L0003')
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0001','Joe','Blow',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0002','John','Doe',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0003','Sue','Happy',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0004','Tina','Turner',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0005','Ike','Turner',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0006','Big','Bird',NULL,NULL,2010/06/25,NULL,NULL)
GO

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0007','Speedy','Gonzales',NULL,NULL,2010/06/25,NULL,NULL)
GO

但是，这些是我收到的错误消息:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DEPARTMEN__Locat__09DE7BCC". The conflict occurred in database "Final_Project", table "dbo.LOCATIONS", column 'Location_ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DEPARTMEN__Locat__09DE7BCC". The conflict occurred in database "Final_Project", table "dbo.LOCATIONS", column 'Location_ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DEPARTMEN__Locat__09DE7BCC". The conflict occurred in database "Final_Project", table "dbo.LOCATIONS", column 'Location_ID'.
The statement has been terminated.
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with date

我不会因为没有立即对解决方案做出回应而犯同样的错误。我不知道勾选绿色复选标记意味着答案令人满意。

感谢您的任何帮助

Answer 1

您有两种不同类型的错误。

首先是您违反了外键约束。有三种方法可以解决这个问题:

找出正确的键应该是什么(例如通过查询 LOCATIONS 表)并将外键更改为正确的值。

在 LOCATIONS 中插入缺失的行插入前的表 DEPARTMENTS .

删除约束(这可能是个坏主意)。

第二个错误更简单 - 您的日期格式不正确。它应该是一个字符串。

'2010-06-25'

完整的查询:

INSERT EMPLOYEES
(Employee_Id,First_Name,Last_Name,Email,PhoneNumber,Hire_Date,Manager_ID,Department_Id)
VALUES
('E0001','Joe','Blow',NULL,NULL,'2010-06-25',NULL,NULL)