我在mysql中的表有什么问题? [英] what is the problem with my tables in mysql?

问题描述

我要创建3张桌子.表"post"和"taxonomy"通过主键和外键连接到"taxonomy_relationship"表，但是我不知道为什么会出现此错误:

1005-无法创建表taxonomy_relationship(错误号:150外键约束格式不正确")

I'm going to create 3 table. table "post" and "taxonomy" are connected to "taxonomy_relationship" table with primary key and foreign key but i don't know why do i get this error:

1005 - Can't create table taxonomy_relationship (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE `post`(
    id int not null AUTO_INCREMENT,
    title varchar(255) not null,
    content TEXT not null,
    PRIMARY KEY(id)
);

CREATE TABLE `taxonomy`(
    id int not null AUTO_INCREMENT,
    tax_title varchar(255) not null,
    type varchar(255) not null,
    PRIMARY KEY(id, tax_title)
);

CREATE TABLE taxonomy_relationship(
    id INT AUTO_INCREMENT,
    post_id int not null,
    title varchar(255) not null,
    PRIMARY KEY(id),
    FOREIGN KEY (post_id) 
        REFERENCES post(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
    FOREIGN KEY (title) 
        REFERENCES taxonomy(tax_title) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

当我使用"SHOW ENGINE INNODB STATUS"显示错误详细信息时，它返回:

when i use "SHOW ENGINE INNODB STATUS" to show error details it returns:

> 2020-01-19 16:08:31 0x2fb8表blog.taxonomy_relationship:
的外键约束错误 外键(标题)
参考分类法(tax_title)
删除级联
关于更新级联
):
在参考表中找不到索引 引用的列显示为第一列或列类型 该表和引用的表中的约束不匹配. 请注意，ENUM和SET的内部存储类型已在 > = InnoDB-4.1.12创建的表，以及旧表中的此类列 不能被新表中的此类列引用. 请参考 https://mariadb.com/kb/zh-CN/library/foreign- keys/获取正确的外键定义. 创建具有外键约束的表blog.taxonomy_relationship失败.在被引用的表中没有索引，被引用的列作为'FOREIGN KEY(标题)附近的第一列出现
参考分类法(tax_title)
删除级联
关于更新级联
)'.

> 2020-01-19 16:08:31 0x2fb8 Error in foreign key constraint of table blog.taxonomy_relationship:
FOREIGN KEY (title)
REFERENCES taxonomy(tax_title)
ON DELETE CASCADE
ON UPDATE CASCADE
):
Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition. Create table blog.taxonomy_relationship with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY (title)
REFERENCES taxonomy(tax_title)
ON DELETE CASCADE
ON UPDATE CASCADE
)'.

推荐答案

您应该在taxonomy表上添加索引KEY title_idx (tax_title):

You should to add index KEY title_idx (tax_title) on taxonomy table :

CREATE TABLE `taxonomy` (
   `id` int NOT NULL AUTO_INCREMENT,
   `tax_title` varchar(255) NOT NULL,
   `type` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `title_idx` (`tax_title`)
);

创建外键之后:

CREATE TABLE taxonomy_relationship(
    id INT AUTO_INCREMENT,
    post_id int not null,
    title varchar(255) not null,
    PRIMARY KEY(id),
    FOREIGN KEY (post_id) 
        REFERENCES post(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    FOREIGN KEY (title) 
        REFERENCES taxonomy(tax_title) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

您可以在taxonomy_relationship中复制title的另一种方法，但是可以通过id字段将其链接:

Another way you can duplicate title in taxonomy_relationship but link it by id field:

CREATE TABLE taxonomy_relationship(
    id INT AUTO_INCREMENT,
    post_id int not null,
    taxonomy_id int not null,
    PRIMARY KEY(id),
    FOREIGN KEY (post_id) 
        REFERENCES post(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    FOREIGN KEY (taxonomy_id) 
        REFERENCES taxonomy(id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

这篇关于我在mysql中的表有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！