时间戳

首页 > 解决方案 > 尝试创建表时无法修复 mysql 中的语法错误

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

问题描述

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 语法有错误；似乎错误在：第 3 行的“entity_id INT(10), PRIMARY KEY (id), FOREIGN KEY (entity_id) )”

我无法理解如何解决它。

而当我添加这个

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"; }

标签： phpmysqlcreate-table

解决方案

如果要添加外键，则需要定义引用表表示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)
)

推荐阅读