INSERT 语句与外键冲突 数据库"HRDATABASE"、表 "dbo.Countries" 、列 'country_id' 中发生冲突

Question

我收到此错误：

INSERT 语句与外键约束"FK__Locations__Count__4222D4EF"冲突。冲突发生在数据库"HRDATABASE"，表"dbo。国家"，"country_id"栏。

我的代码：

CREATE DATABASE HRDATABASE
USE HRDATABASE
CREATE Table Regions
(
Region_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Region_Name VARCHAR(35) DEFAULT NULL
)
INSERT INTO Regions(Region_ID,Region_Name) VALUES(1,'Europe');
INSERT INTO Regions(Region_ID,Region_Name) VALUES(2,'USA');
INSERT INTO Regions(Region_ID,Region_Name) VALUES(3,'ITALY');
SET IDENTITY_INSERT Regions ON;
CREATE TABLE Countries
(
Country_id CHAR (2) PRIMARY KEY,
Country_name VARCHAR (40) DEFAULT NULL,
Region_id INT NOT NULL,
FOREIGN KEY (region_id) REFERENCES regions (region_id) 
ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO countries (Country_id, Country_name, Region_id) 
VALUES ('AR', 'Argentina', 2);
INSERT INTO countries (Country_id, Country_name, Region_id) 
VALUES ('AU', 'Australia', 3);
INSERT INTO countries (Country_id, Country_name, Region_id) 
VALUES ('BE', 'Belgium', 1);
CREATE TABLE Locations
(
Location_ID INT IDENTITY(1,1) PRIMARY KEY,
Street_Address VARCHAR (40) DEFAULT NULL,
Postal_Code VARCHAR (12) DEFAULT NULL,
City VARCHAR (30) NOT NULL,
State_Province VARCHAR (25) DEFAULT NULL,
Country_ID CHAR (2) NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) 
ON DELETE CASCADE ON UPDATE CASCADE
);
SET IDENTITY_INSERT locations ON;
INSERT INTO Locations (Location_ID, Street_Address, Postal_Code, City, State_Province, Country_ID) 
VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO Locations (Location_ID, Street_Address, Postal_Code, City, State_Province, Country_ID) 
VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO Locations (Location_ID, Street_Address, Postal_Code, City, State_Province, Country_ID) 
VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
SET IDENTITY_INSERT locations OFF;

Answer 1

您在营业地点表中插入了country_id"US"，但您的国家/地区表中没有该country_id。

在国家/地区中插入"美国"国家/地区，或者在"位置"表中删除外键FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE