外键约束不会对MySQL表创建

Question

确定这里是我的两个表是具有冲突：

CREATE TABLE visit_physician (
    visit_id INT(11) UNSIGNED NOT NULL, 
    physician_id INT(11) UNSIGNED NOT NULL, 
    physician_role INT(11) UNSIGNED NOT NULL, 
    PRIMARY KEY(visit_id, physician_id) 
); 

CREATE TABLE physician_role (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT KEY, 
    name VARCHAR(50) NOT NULL, 
    UNIQUE(name) 
); 

ALTER TABLE visit_physician 
ADD CONSTRAINT FK_roleid 
FOREIGN KEY (physician_role) REFERENCES physician_role(id) 
ON UPDATE CASCADE 
ON DELETE CASCADE; 

physician_role和ID是相同的数据类型和大小，一切..所以为什么不创建？我的其他外键分配没有问题..

Answer 1

我测试了你的语句在MySQL 5.5.29，他们工作正常。请注意，两个表都必须使用InnoDB存储引擎来支持外键。如果不是，MySQL会解析，但忽略这些限制的声明。

在我的实例中，默认的存储引擎是InnoDB。您的CREATE TABLE语句不指定存储引擎，因此它们将使用默认值。检查默认存储引擎是在什么环境：

mysql> show global variables like 'default_storage_engine'; 
+------------------------+--------+ 
| Variable_name   | Value | 
+------------------------+--------+ 
| default_storage_engine | InnoDB | 
+------------------------+--------+ 

检查使用你的表格与SHOW TABLE STATUS LIKE '%physician%'存储引擎。看看输出的引擎列：

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time   | Update_time | Check_time | Collation   | Checksum | Create_options | Comment | 
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 
| physician_role | InnoDB |  10 | Compact | 0 |    0 |  16384 |    0 |  16384 |   0 |    1 | 2013-02-25 15:56:26 | NULL  | NULL  | latin1_swedish_ci |  NULL |    |   | 
| visit_physician | InnoDB |  10 | Compact | 0 |    0 |  16384 |    0 |  16384 |   0 |   NULL | 2013-02-25 15:56:26 | NULL  | NULL  | latin1_swedish_ci |  NULL |    |   | 
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 

如果发动机的MyISAM或其他存储引擎，它很可能是约束的创建将无法正常工作。

如果你想改变存储引擎InnoDB的，你可以这样做：

mysql> ALTER TABLE visit_physician ENGINE=InnODB; 
mysql> ALTER TABLE physician_role ENGINE=InnODB; 

那么你应该能够重新执行约束的创建。但是，如果表中已经有不符合约束的数据，则可能会导致问题。

但在任何此类更改之前，我会鼓励您测试您的应用程序是否仍适用于不同的存储引擎。它很可能会正常工作，但测试总是一个好主意。

---

当您尝试添加外键时会发生什么？如果它给出这样的错误：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint 
fails (`test`.`#sql-c67_52`, CONSTRAINT `FK_roleid` FOREIGN KEY (`physician_role`) 
REFERENCES `physician_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) 

那么你可以有一些值已经在表中不满足约束。

关于存储引擎和数据类型的其他可能性也可能导致错误，但我们已经排除了，因为您说这些表都是InnoDB，并且数据类型看起来相同，并且在我测试它时会起作用。

Answer 2

我觉得可能有一些东西需要与一个表中的两个外键约束，但我加了他们在创建表，以便阅读：

CREATE TABLE visit_physician (
    visit_id INT(11) UNSIGNED NOT NULL, 
    physician_id INT(11) UNSIGNED NOT NULL, 
    physician_role INT(11) UNSIGNED NOT NULL, 
    PRIMARY KEY(visit_id, physician_id), 
    CONSTRAINT FOREIGN KEY (physician_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE, 
    CONSTRAINT FOREIGN KEY (physician_role) REFERENCES physician_role (id) ON DELETE CASCADE ON UPDATE CASCADE 
); 

以防万一，如果有人在具有奇怪的问题，我有..