php - 尝试创建表时无法修复 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)
)

我得到一个错误

You have an error in your SQL syntax; it seems the error is around: ' entity_id INT(10), PRIMARY KEY (id), FOREIGN KEY (entity_id) )' at line 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)
)