尝试创建表时无法修复 mysql 中的语法错误

Question

CREATE TABLE category(

  id int(10) NOT NULL AUTO_INCREMENT,

  entity_type varchar(32),

  entity_id INT(10),

  PRIMARY KEY (id),

  FOREIGN KEY (entity_id)

)

我收到一个错误

您的 SQL 语法有错误；似乎错误在：' entity_id INT(10), PRIMARY KEY (id), FOREIGN KEY (entity_id) )' 在第 3 行

我无法理解如何修复它。

而当我添加这个

CREATE TABLE `Image` (

  `Id [PK]` int (10)                               ,

  `EntityType` varchar(32),

  `EntityId [FK]` int(10)

);

上面的代码修复了错误

下面的代码即使在我尝试先创建图像和类别表然后在用户表中添加与它的关系之后也给出了外键约束

$sql_image = 'CREATE TABLE IF NOT EXISTS image (

    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    entity_type VARCHAR(32) NOT NULL,

    entity_id INT(10) UNSIGNED NOT NULL,

    PRIMARY KEY (id) )';

if ($db->database->createTable($sql_image)) {   echo "Image Table Created Successfully"; }

$sql_category = 'CREATE TABLE IF NOT EXISTS category (

    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    entity_type VARCHAR(32) NOT NULL,

    entity_id INT(10) UNSIGNED NOT NULL,

    PRIMARY KEY (id) );';

if ($db->database->createTable($sql_category)) {

    echo "Category Table Created Successfully"; }

$sql_user = 'Create TABLE IF NOT EXISTS user(

    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    first_name varchar(255),

    last_name varchar(255),

    email varchar(255),

    category int(10),

    status boolean,

    user_profile_photo int(10),

    FOREIGN KEY (user_profile_photo) references image(entity_id),

    FOREIGN KEY (category) references category(entity_id) );';

if ($db->database->createTable($sql_user)) {

    echo "User Table Created Successfully"; }

Answer 1

如果要添加外键，则需要定义引用表，表示该表entity_id属于哪个表CREATE TABLE category(  id int(10) NOT NULL AUTO_INCREMENT,  entity_type varchar(32),  entity_id INT(10),  PRIMARY KEY (id),  FOREIGN KEY (entity_id) REFERENCES Entity(entity_id))