mysql - 键不在表中，但它是

Question

错误:插入或更新表“salesorderheader”违反了外键约束“salesorderheader_customerid_fkey”详细信息:键 (customerid)=(29825) 不存在于表“customer”中。********** 错误 **********

但 29825 存在于 Customer.txt 文件中。

代码如下:

DROP TABLE IF EXISTS PersonAddress;
DROP TABLE IF EXISTS SalesOrderDetail;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS SalesOrderHeader;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS ProductCategory;
DROP TABLE IF EXISTS Address;
DROP TABLE IF EXISTS CreditCard;

CREATE TABLE Person(
PersonID INT NOT NULL,
Title varchar(255),
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
PRIMARY KEY (PersonID)
);



CREATE TABLE ProductCategory(
ProductCategoryID INT,
Name VARCHAR(255) NOT NULL,
PRIMARY KEY (ProductCategoryID)
);



CREATE TABLE Address(
AddressID INT NOT NULL,
AddressLine1 VARCHAR(255) NOT NULL,
AddressLine2 VARCHAR(255),
City VARCHAR(255) NOT NULL,
PostalCode VARCHAR(255) NOT NULL,
PRIMARY KEY (AddressID)
);



CREATE TABLE CreditCard(
CreditCardID INT NOT NULL,
CardType VARCHAR(255) NOT NULL,
CardNumber BIGINT NOT NULL,
ExpMonth INT NOT NULL,
ExpYear INT NOT NULL,
PRIMARY KEY (CreditCardID)
);



CREATE TABLE PersonAddress(
PersonID INT NOT NULL,
AddressID INT NOT NULL,
PRIMARY KEY (AddressID),
FOREIGN KEY (AddressID) REFERENCES Address(AddressID),
FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);



CREATE TABLE Customer(
CustomerID INT NOT NULL,
PersonID INT,
PRIMARY KEY (CustomerID),
FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);



CREATE TABLE SalesOrderHeader(
SalesOrderID INT NOT NULL,
OrderDate VARCHAR(255) NOT NULL,
DueDate VARCHAR(255) NOT NULL,
ShipDate VARCHAR(255) NOT NULL,
OnlineOrderFlag INT NOT NULL,
CustomerID INT NOT NULL,
CreditCardID INT,
SubTotal FLOAT NOT NULL,
TaxAmt FLOAT NOT NULL,
Freight FLOAT NOT NULL,
TotalDue FLOAT NOT NULL,
PRIMARY KEY (SalesOrderID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (CreditCardID) REFERENCES CreditCard(CreditCardID)
);



CREATE TABLE Product(
ProductID INT NOT NULL,
Name VARCHAR(255) NOT NULL,
SafetyStockLevel INT NOT NULL,
ReorderPoint INT NOT NULL,
StandardCost FLOAT NOT NULL,
ListPrice FLOAT NOT NULL,
ProductCategoryID INT,
PRIMARY KEY (ProductID),
FOREIGN KEY (ProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)
);



CREATE TABLE SalesOrderDetail(
SalesOrderID INT NOT NULL,
SalesOrderDetailID INT NOT NULL,
OrderQty INT NOT NULL,
ProductID INT NOT NULL,
UnitPrice FLOAT NOT NULL,
UnitPriceDiscount FLOAT NOT NULL,
LineTotal FLOAT NOT NULL,
PRIMARY KEY (SalesOrderDetailID),
FOREIGN KEY (SalesOrderID) REFERENCES SalesOrderHeader(SalesOrderID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);



COPY Person
FROM 'C:\Users\Public\Vaseis\Person.txt' DELIMITER ',' CSV HEADER;

COPY ProductCategory
FROM 'C:\Users\Public\Vaseis\ProductCategory.txt' DELIMITER ',' CSV HEADER;

COPY Address
FROM 'C:\Users\Public\Vaseis\Address.txt' DELIMITER ',' CSV HEADER;

COPY CreditCard
FROM 'C:\Users\Public\Vaseis\CreditCard.txt' DELIMITER ',' CSV HEADER;

COPY PersonAddress
FROM 'C:\Users\Public\Vaseis\PersonAddress.txt' DELIMITER ',' CSV HEADER;

COPY SalesOrderHeader
FROM 'C:\Users\Public\Vaseis\SalesOrderHeader.txt' DELIMITER ',' CSV HEADER;

COPY Product
FROM 'C:\Users\Public\Vaseis\Product.txt' DELIMITER ',' CSV HEADER;

COPY SalesOrderDetail
FROM 'C:\Users\Public\Vaseis\SalesOrderDetail.txt' DELIMITER ',' CSV HEADER;

29825 in the .txt

Answer 1

您没有填充 Customer 表:

COPY Customer
FROM 'C:\Users\Public\Vaseis\Customer.txt' DELIMITER ',' CSV HEADER;

COPY SalesOrderHeader
FROM 'C:\Users\Public\Vaseis\SalesOrderHeader.txt' DELIMITER ',' CSV HEADER;