Why do I keep getting foreign key errors when trying to create this table?

Question

I am trying to create tables for a database. Everything is fine except for when I try to make my last joining table, it keeps giving me a foreign key error "Create table 'refactor_test/ads_categories' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns."

Here are the parameters I used to create each table, the one that keeps throwing errors is ads_categories:

    create table ads
    (
      id          int unsigned not null auto_increment,
      user_id     int unsigned not null,
      title       varchar(100) not null,
      description varchar(500),
      primary key (id),
      foreign key (user_id) references refactor_test.users (id)
    );

    create table users
    (
      id       int unsigned not null auto_increment,
      username varchar(25)  not null,
      password varchar(100) not null,
      email    varchar(40)  not null,
      primary key (id)
    );

    create table categories
    (
      id          int unsigned auto_increment not null,
      category    varchar(50)                 not null,
      category_id int unsigned                not null,
      primary key (id)
    );

    create table ads_categories
    (
      id          int unsigned auto_increment not null,
      ad_id       int unsigned                not null,
      ad_category int unsigned                not null,
      primary key (id),
      foreign key (ad_id) references refactor_test.ads (id),
      foreign key (ad_category) references refactor_test.categories (category_id)
    );

From the [docs](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html): "Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys. " — Paul Spiegel, Feb 16 '19 at 19:40

score 2 · Accepted Answer · answered Feb 16 '19 at 19:18

2

A foreign key links to the primary key of the target table. Here you're trying to link to category_id on the categories table:

foreign key (ad_category) references refactor_test.categories (category_id)

But that's not the primary key for that table. id is:

foreign key (ad_category) references refactor_test.categories (id)

(Having both an id and a category_id in the categories table is pretty confusing actually. I suspect there's a design mistake there.)

answered Feb 16 '19 at 19:18

David

208,112
36
198
279

3

i think its possible to use a non primary key as foreign key as long as its a UNIQUE field as stated in this answer https://stackoverflow.com/questions/8118739/mysql-non-primary-foreign-key – Iván Quiñones Feb 16 '19 at 19:23
@IvánQuiñones: Is it? Interesting. I suspect that may differ by RDBMS. Seems unintuitive to me, but I suppose it could have its uses. – David Feb 16 '19 at 19:23
shouldnt, the unique fields conform with the uniqueness (pun intended) required to point to one and only one element, the principle for indexing – Iván Quiñones Feb 16 '19 at 19:26
2

In MySQL the referenced column just need to be indexed. However - if it's not UNIQUE and NOT NULL the behaviour might be unpredictable. – Paul Spiegel Feb 16 '19 at 19:26
2

@IvánQuiñones: Agreed. As long as something is unique then semantically it should be able to identify a record. Though in this particular case I still suspect that having both a `categories.id` and a `categories.category_id` is *probably* a design error and will likely lead to further confusion in the data model. – David Feb 16 '19 at 19:26
@David indeed, it looks like categories is meant to link ads with categories, if this is the case a better option is to have a many-to-many junction table wich should include only the keys of both tables and not a key for itself, like this https://en.wikipedia.org/wiki/Associative_entity – Iván Quiñones Feb 16 '19 at 19:32

score 0 · Answer 2 · answered Feb 16 '19 at 19:21

0

Might be because categories.category_id is nor the key of the categories table and nor a unique field

answered Feb 16 '19 at 19:21

Iván Quiñones

501
4
12

Why do I keep getting foreign key errors when trying to create this table?

2 Answers2