MariaDB - Create many to many relationship table between two entities

Question

I am having a problem creating a many to many relationship table in MariaDB. I have tried using the workbench, manual creation scripts and the "Show Create Table xxxxxxx;" from another already created n to n tables but the result is always the same, the following error:

Error Code: 1005. Can't create table `asi_234_api_establecimientos`.`oe_modalidad` (errno: 150 "Foreign key constraint is incorrectly formed")

The code I am using to create the table:

CREATE TABLE `oe_modalidad` (
  `oferta_establecimiento_id` bigint(20) NOT NULL,
  `modalidad_id` bigint(20) NOT NULL,
  KEY `fk_oe_modalidades_oferta_establecimiento1_idx` (`oferta_establecimiento_id`),
  KEY `fk_oe_modalidad_modalidad1_idx` (`modalidad_id`),
  CONSTRAINT `fk_oe_modalidad_modalidad1` FOREIGN KEY (`modalidad_id`) REFERENCES `modalidad` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_oe_modalidades_oferta_establecimiento1` FOREIGN KEY (`oferta_establecimiento_id`) REFERENCES `oferta_establecimiento` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB

I have tried running this in two different versions of MariaDB, 10.0.38 and 5.5.60 but I keep getting the same error.

Show the definitions of the `modalidad` and `oferta_establecimiento` tables. — Barmar, Jun 07 '19 at 16:02
Search SO for your error message, you'll see the usual reasons. — Barmar, Jun 07 '19 at 16:07
You might like to read this checklist for proper foreign keys: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Jun 07 '19 at 16:37
Sorry for the lack of information. Indeed it was the difference in type for both keys. Thanks for the help. @Barmar — Guido, Jun 07 '19 at 17:35
Don't bother with FKs on a many:many table. It's no big deal if there is a dangling reference. — Rick James, Jun 08 '19 at 05:54

score 4 · Accepted Answer · answered Jun 07 '19 at 16:01

4

This is a very short example:

create table Table1(
    id int auto_increment key,
    name varchar(50) not null
);

create table Table2(
    id int auto_increment key,
    name varchar(50) not null
);

create table Table3(
    idTable1 int not null,
    idTable2 int not null,
    primary key(idTable1, idTable2),
    CONSTRAINT fk_table3_table1 foreign key (idTable1) references Table1 (id),
    CONSTRAINT fk_table3_table2 foreign key (idTable2) references Table2 (id)
);

And remember, Table1 and Table2 primary key have to be same type of Table3 foreign key.

answered Jun 07 '19 at 16:01

Graiton

78
7

Can you explain what he did wrong and needs to fix? A working example doesn't really do that. – Barmar Jun 07 '19 at 16:03
I think type aren't same, but I can't know it because he didn't post other tables. – Graiton Jun 07 '19 at 16:07
That's probably true, but until he clarifies, we can't really answer. – Barmar Jun 07 '19 at 16:10
Thank you very much @Graiton the problem was the key type. One was bigint(20) and the other one just int. – Guido Jun 07 '19 at 17:32

MariaDB - Create many to many relationship table between two entities

1 Answers1