解决报错：ERROR 1005 (HY000): Can't create table 'market.orders' (errno: 150)

桃扇骨 2023-10-11 09:49 25阅读 0赞

1.描述问题：

在这里我新建了两张表（customers\_info和orders）

表一：customers\_info

CREATE TABLE customers_info
    (
    c_num INT(11) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
    c_name VARCHAR(50),
    c_contact VARCHAR(50),
    c_city VARCHAR(50),
    c_birth DATETIME NOT NULL
    );

![1444822-20190918211832225-1899006908.png][]

此时我们在新建表二orders时候，题目要求c\_id的数据类型为VARCHAR(50)，却要求我给他设置一个外键约束，关联到customers\_info表中的c\_num。

CREATE TABLE orders 
    (
    o_num INT(11) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
    o_date DATE,
    c_id VARCHAR(50),
    CONSTRAINT fk_c_num FOREIGN KEY(c_id) REFERENCES customers_info(c_num) 
    );

**执行这个时候，会出现报错：ERROR 1005 (HY000): Can't create table 'market.orders' (errno: 150)**

**出现问题的大致情况：**

**1、外键的引用类型不一样，如主键是int外键是char**

**2、找不到主表中引用的列**

**3、主键和外键的字符编码不一致，也可能存储引擎不一样**

**这里我遇到的是第一种和第三种情况，如图所示：**

![1444822-20190918212141761-2057336977.png][]

**解决方式：**

**1.修改两张表的存储引擎相同**

**2.修改两者的数据类型一致**

![1444822-20190918213233861-1006371796.png][]

![1444822-20190918213251536-1061807025.png][]

MariaDB [market]> show create table customers_info\G
    *************************** 1. row ***************************
           Table: customers_info
    Create Table: CREATE TABLE `customers_info` (
      `c_num` int(11) NOT NULL AUTO_INCREMENT,
      `c_name` varchar(70) DEFAULT NULL,
      `c_birth` datetime NOT NULL,
      `c_phone` varchar(50) DEFAULT NULL,
      `c_gender` char(1) DEFAULT NULL,
      PRIMARY KEY (`c_num`),
      UNIQUE KEY `c_num` (`c_num`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    MariaDB [market]> show create table orders\G
    *************************** 1. row ***************************
           Table: orders
    Create Table: CREATE TABLE `orders` (
      `o_num` int(11) NOT NULL AUTO_INCREMENT,
      `o_date` date DEFAULT NULL,
      `c_id` int(50) DEFAULT NULL,
      PRIMARY KEY (`o_num`),
      UNIQUE KEY `o_num` (`o_num`),
      KEY `fk_c_num` (`c_id`),
      CONSTRAINT `fk_c_num` FOREIGN KEY (`c_id`) REFERENCES `customers_info` (`c_num`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/python-wen/p/11545515.html

[1444822-20190918211832225-1899006908.png]: https://img2018.cnblogs.com/blog/1444822/201909/1444822-20190918211832225-1899006908.png
[1444822-20190918212141761-2057336977.png]: https://img2018.cnblogs.com/blog/1444822/201909/1444822-20190918212141761-2057336977.png
[1444822-20190918213233861-1006371796.png]: https://img2018.cnblogs.com/blog/1444822/201909/1444822-20190918213233861-1006371796.png
[1444822-20190918213251536-1061807025.png]: https://img2018.cnblogs.com/blog/1444822/201909/1444822-20190918213251536-1061807025.png