MySQL错误1005？ [英] MySQL Error 1005?

问题描述

我正在尝试创建一个数据库，但出现了一个奇怪的错误...这是我的代码

I'm trying to create a database, but am getting a strange error... This is my code

DROP TABLE IF EXISTS `Person`;
DROP TABLE IF EXISTS `Address`;
DROP TABLE IF EXISTS `Email`;
DROP TABLE IF EXISTS `Airport`;
DROP TABLE IF EXISTS `Customer`;

CREATE TABLE Address
(
AddressID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(AddressID),
AddressStreet VARCHAR(255),
AddressCity VARCHAR(255),
AddressState VARCHAR(255),
AddressZip VARCHAR(255),
AddressCountry VARCHAR(255)
);


CREATE TABLE Email
(
EmailID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(EmailID),
EmailAddress VARCHAR(255)
);

CREATE TABLE Person
(
PersonID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(PersonID),
PersonCode VARCHAR(255),
PersonLastName VARCHAR(255),
PersonFirstName VARCHAR(255),
AddressID INT NOT NULL,
FOREIGN KEY `fk_Person_to_Address` (AddressID) REFERENCES Address(AddressID),
PersonPhone VARCHAR(255),
EmailID INT NOT NULL,
FOREIGN KEY `fk_Person_to_Email` (EmailID) REFERENCES Email(EmailID)
);

CREATE TABLE Airport
(
AirportID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(AirportID),
AirportCode VARCHAR(255),
AirportName VARCHAR(255),
AddressID INT NOT NULL,
FOREIGN KEY `fk_Airport_to_Address` (AddressID) REFERENCES Address(AddressID),
AirportLatDeg INT NOT NULL,
AirportLatMin INT NOT NULL,
AirportLongDeg INT NOT NULL,
AirportLongMin INT NOT NULL,
AirportPassFacilityFee FLOAT NOT NULL
);

CREATE TABLE Customer
(
CustomerID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(CustomerID),
CustomerCode VARCHAR(255),
CustomerType VARCHAR(255),
PrimaryContact VARCHAR(255),
FOREIGN KEY `fk_Customer_to_Person` (PrimaryContact) REFERENCES Person(PersonID),
CustomerName VARCHAR(255),
CustomerAirlineMiles FLOAT NOT NULL
);

一切都很好，直到我到达Customer表，我尝试重命名一个外键。这是我得到的错误...

Everything goes great until I get to the Customer table and I try to rename one of the foreign keys. This is the error I'm getting...

Error Code: 1005. Can't create table 'myName.Customer' (errno: 150)

我试图看看这个错误的一些其他例子，理解问题。是因为我想在客户表中重命名外键？我想要的是从Person表中获取PersonID，但在Customer表中将其重命名为primaryContact。

I've tried to look at some other examples of this error but am not understanding the problem. Is it because I'm trying to rename a foreign key in the Customer table? What I want is to get the PersonID from the Person table, but rename it primaryContact in the Customer table. How can I fix this?

推荐答案

创建外键时，数据类型应该与主键的数据类型匹配。更改 Customer 表中的 PrimaryContact 字段以匹配 PersonId ，在这种情况下， int ：

When creating a foreign key, the data types should match that of the primary key. Change the PrimaryContact field in the Customer table to match the data type of PersonId, in this case, int:

CREATE TABLE Customer
(
CustomerID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(CustomerID),
CustomerCode VARCHAR(255),
CustomerType VARCHAR(255),
PrimaryContact int,
FOREIGN KEY `fk_Customer_to_Person` (PrimaryContact) REFERENCES Person(PersonID),
CustomerName VARCHAR(255),
CustomerAirlineMiles FLOAT NOT NULL
);

列必须与手册页中使用FOREIGN KEY约束：

外键和引用键中的相应列必须
具有相似的数据类型。整型类型的大小和符号必须是
相同。字符串类型的长度不必相同。对于
非二进制（字符）字符串列，字符集和排序规则
必须相同。

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

这篇关于MySQL错误1005？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！