0

I'm trying to create a many to many relation table for products and categories named product_categories.

Using: MySQL, INNODB. IDs are UUID4 ,using char(36) for the ID fields.

create table product_categories
(
    product_id  char(36) not null,
    category_id char(36) not null,
    primary key (product_id, category_id),
    constraint fk_product_categories_category
        foreign key (category_id) references categories (id)
            on delete cascade,
    constraint fk_product_categories_product
        foreign key (product_id) references products (id)
            on delete cascade
);

Problem seems to be category_id as if I remove it, the table is created without a problem.

Category table is also matching the ID;

CREATE TABLE `categories` (
  `id` char(36) NOT NULL,
  ....
)

Unfortunately I still get the following error;[HY000][1005] Can't create table product_categories (errno: 150 "Foreign key constraint is incorrectly formed"). What am I missing here?

Revenant
  • 2,942
  • 7
  • 30
  • 52
  • 1
    Check your collation on your tables? – Ilgıt Yıldırım Aug 30 '22 at 13:10
  • `categories (id)` must be defined as unique (maybe primary key). `categories (id)` and `product_categories (category_id)` must have the same charset and collation settings. – Akina Aug 30 '22 at 13:30
  • @Akina sorry I have not mentioned it, it is already a PRIMARY key. – Revenant Aug 30 '22 at 13:38
  • Provide complete SHOW CREATE TABLE output for ref. tables in question. Provide complete SHOW CREATE TABLE output for problematic table with commented problematic FK constraint. Specify precise MySQL version. – Akina Aug 30 '22 at 13:56
  • 1
    @IlgıtYıldırım you are right! This was a collation issue. The moment all tables had the same collation, the problem was fixed. I didn't notice the difference as the previous tables were created by an ORM and the new table was coming from a mysqldump export and had different collation. Can you please add as an answer so I can accept your solution? – Revenant Aug 30 '22 at 14:36
  • Does this answer your question? [mysql Foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) – philipxy Sep 08 '22 at 09:54

2 Answers2

0

Firstly you need to create Tables that you are trying to link

The "Linking table" should have constraints like this

primary key (id_1, id_2),
    constraint 'constraint fk1_name'
        foreign key (id_1) references 'table_where_id_1_is' (id_1),
    constraint 'constraint fk2_name'
        foreign key (id_2) references 'table_where_id_2_is' (id_2)

if you need you can add ON UPDATE/DELETE CASCADE

Hope it helps, I grabbed this format from my project that has multiple linking tables that work so it should help ya out.

Also, keep in mind I am guessing you have id_1 or 2 samely named on both linking and the table that you are trying to link, if you don't it would be a good practice to do in the future. The left one is from the linking table and the right is from the table that you are linking...

Also here is SQL Code for one of my linking table (don't mind my language it gibberish xD)

Image

Flexy
  • 3
  • 2
  • Thank you, the tables already are there, problem seems to be `categories` constraint. I'm looking into collation issue as suggested in the question's comments. – Revenant Aug 30 '22 at 13:39
0

Your tables collation should match too. If it doesn't the given message is a little confusing but actually stating the truth behind it. Due to collation being different the foreign key wouldn't be matching.

Ilgıt Yıldırım
  • 443
  • 4
  • 10