mysql 中的外键问题(错误 1005) [英] Foreign key issue in mysql (error 1005)

问题描述

我尝试创建以下表格:

客户:

CREATE TABLE Customer(customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                      surname VARCHAR(15) NOT NULL,
                      forename VARCHAR(15) NOT NULL,
                      DOB TIMESTAMP NOT NULL,
                      phone_no VARCHAR(20) NOT NULL,
                      email_address VARCHAR(30),
                      postcode VARCHAR(15) NOT NULL, 
                      PRIMARY KEY(customer_id)) ENGINE=INNODB;

当我之后尝试创建预订"表时，出现错误

When I attempt to create the 'Booking' table afterwards, I get an error

错误 1005(HY000):无法创建表 'Test.Booking' (errno:150)"

"ERROR 1005(HY000): can't create table 'Test.Booking' (errno:150)"

我在线检查了错误，发现它与我试图在表之间建立的外键连接有关；我将在下面给出.

I checked the error online and found out that it was relating to the Foreign key connection that I attempted to make between the tables; which I will give below.

预订:

CREATE TABLE Booking (booking_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
                      customer_id INT NOT NULL,
                      staying_from TIMESTAMP,
                      staying_until TIMESTAMP,
                      cost INT,
                      PRIMARY KEY(booking_id),
                      FOREIGN KEY(customer_id) REFERENCES Customer(customer_id)
                      ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE=INNODB;

推荐答案

如 外键约束:

外键定义受以下条件的约束:

Foreign keys definitions are subject to the following conditions:

[删除]

• 外键和引用键中的对应列必须在InnoDB内部具有相似的内部数据类型，以便它们可以在不进行类型转换的情况下进行比较.整数类型的大小和符号必须相同.字符串类型的长度不必相同.对于非二进制(字符)字符串列，字符集和排序规则必须相同.

• Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. 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.

在 Customer 中有 INT UNSIGNED 而在 Booking 中有 INT.

In Customer you have INT UNSIGNED whereas in Booking you have INT.

这篇关于mysql 中的外键问题(错误 1005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！