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

I get an error

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

I am unable to understand on how to fix it.

Whereas when I add this

CREATE TABLE `Image` (
  `Id [PK]` int (10)                               ,
  `EntityType` varchar(32),
  `EntityId [FK]` int(10)
);

the above code fixes the error

Below is the code which gives Foreign key constraints even after I tried creating image and category table first and then adding relation to it in the User Table

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

The last line it's wrong. Try something like this:

FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)'
FOREIGN KEY (product_category, product_id)
      REFERENCES second_table(category, id)

If you want a foreign key to be added then you need to define the reference table means in which table the entity_id belongs

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)
)

2 errors that I can see 1) 'MySQL requires indexes on foreign keys and referenced keys'. MySQL will create keys on the referencing table(users) if you do not but you have to create them on the referenced tables on entity_id(image,category) 2) 'Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same'

see https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

This syntaxs and generates the tables (you need to decide what kind of key k1 and k2 should be):-

drop table if exists us;
drop table if exists i;
drop table if exists c;

CREATE TABLE IF NOT EXISTS i (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    entity_type VARCHAR(32) NOT NULL,
    entity_id INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (id) );
alter table i
    add key k1(entity_id);

CREATE TABLE IF NOT EXISTS c (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    entity_type VARCHAR(32) NOT NULL,
    entity_id INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (id) );
alter table c
    add key k2(entity_id);

Create TABLE IF NOT EXISTS us(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name varchar(255),
    last_name varchar(255),
    email varchar(255),
    category int(10) unsigned,
    status boolean,
    user_profile_photo int(10) unsigned,
    FOREIGN KEY fk1(user_profile_photo) references i(entity_id),
    FOREIGN KEY (category) references c(entity_id) 
    );

1. Verify that both table columns are defined with same data type.
2. Verify that both table and their columns have same collation charset should be same e.g. utf-8
   • Even if tables have same collation , columns still could have different one.
3. Verify that both columns have the same signing definition. If the referencing column is int(10) unsigned so should be the referencing column.

In my case column user_profile_photo from user table and id from image table were having different signing definition. hence it was not adding foreign key. FOREIGN KEY (user_profile_photo) references image(id), FOREIGN KEY (category) references category(id)

$sql_image = 'CREATE TABLE IF NOT EXISTS image (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, // int(10) UNSIGNED should be same as that in user table below.
    entity_type VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
)';

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

$sql_category = 'CREATE TABLE IF NOT EXISTS category (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, // int(10) UNSIGNED should be same as that in user table below.
    entity_type VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
);';

if ($db->database->createTable($sql_category)) {
    echo "Category Table Created Successfully<br><br>";
}

$sql_user = 'CREATE TABLE IF NOT EXISTS user(
    id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name varchar(255),
    last_name varchar(255),
    email varchar(255),
    category int(10) UNSIGNED NOT NULL, //int(10) UNSIGNED should be match id from category table.
    status boolean,
    user_profile_photo int(10) UNSIGNED NOT NULL,
    FOREIGN KEY (user_profile_photo) references image(id),
    FOREIGN KEY (category) references category(id)
);';

if ($db->database->createTable($sql_user)) {
    echo "User Table Created Successfully<br><br>";
}