无法在 FOREIGN KEY 上创建表 (errno: 150)

2022-01-20 00:00:00 sql errno foreign-keys php mysql

我看到很多相同的问题，但我无法解决我的问题.

I saw a lot of same question but I couldn't solve my case.

如果我运行这段代码:

<?php
include_once($_SERVER['DOCUMENT_ROOT'].'/config.php');

$servername = HOST;
$username = USERNAME;
$password = PASSWORD;
$dbname = DB;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS Articls (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,     
            name VARCHAR(254) COLLATE utf8_persian_ci NOT NULL      
) DEFAULT COLLATE utf8_persian_ci";

if ($conn->query($sql) === TRUE) {
    echo "Table Articls created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

/////////////////////////////////////////////////////////////////////////

$sql = "CREATE TABLE IF NOT EXISTS Tags (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,  
            id_articls INT(10) UNSIGNED NOT NULL,   
            name VARCHAR(256) COLLATE utf8_persian_ci NOT NULL,           
            FOREIGN KEY(Tags.id_articls) REFERENCES Articls(Articls.id)         
) DEFAULT COLLATE utf8_persian_ci";

if ($conn->query($sql) === TRUE) {
    echo "Table Tags created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

我收到此错误:(如果我删除 FOREIGN KEY 它可以工作)

I get this error: ( If I remove FOREIGN KEY it works)

成功创建表格文章 创建表格时出错:无法创建表 'admin_wepar.Tags' (errno: 150)

Table Articls created successfully Error creating table: Can't create table 'admin_wepar.Tags' (errno: 150)

编辑

如果将 更改为 Articls.id 和 Tags.id_articls 我收到此错误:

If a change into Articls.id and Tags.id_articls I got this error:

表格文章创建成功错误创建表格:您有一个SQL 语法错误；检查与您对应的手册MySQL 服务器版本，用于在 'FOREIGN KEY 附近使用正确的语法(Tags.id_articls)参考文章(Articles.id))DEFAULT COLLA' at第 5 行

Table Articls created successfullyError creating table: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (Tags.id_articls) REFERENCES Articls(Articls.id) ) DEFAULT COLLA' at line 5

推荐答案

Tags.id_articls 是一个 有符号 整数，而 Artcl.id 是无符号整数.MySQL 要求引用字段的类型完全相同.使 Tags.id_articles 无符号以使其工作.

Tags.id_articls is a signed integer while Articl.id is an unsigned integer. MySQL requires referencing field to be exactly the same type. Make Tags.id_articls unsigned to have it work.

另外，列列表中的表名在 MySQL 中是不允许的.总是很清楚是指哪个表:首先是引用表，然后是引用表.所以改变

Additionally, the table names in the column lists are not allowed in MySQL. It is always clear which table is meant: first the referencing table and then the referenced table. So change

FOREIGN KEY(Tags.id_articls) REFERENCES Articls(Articls.id)

进入

FOREIGN KEY(id_articls) REFERENCES Articls(id)

它会起作用的.

相关文章