Can't create table with foreign key Maria DB

Question

I'm having hard time creating a table in maria db with foreign key and I can't figure it out. I got that message : "Can't create table evanightdb.events_liked (errno: 150 "Foreign key constraint is incorrectly formed")".

The table I try to create :

CREATE TABLE events_liked (
    id INT NOT NULL  AUTO_INCREMENT,
    id_events int(11),
    title_event text,
    event_liked int NOT NULL,
    PRIMARY KEY ID,
    FOREIGN KEY (id_events) REFERENCES event_details(`id`),
    FOREIGN KEY (title_event) REFERENCES event_details(`title`)
    )

And this is the table "event_details".

CREATE TABLE `event_details` (
  `id` int(11) NOT NULL,
  `event_url` varchar(300) NOT NULL,
  `url` varchar(300) NOT NULL,
  `title` text NOT NULL,
  `description` text DEFAULT NULL,
  `event_image_url` text DEFAULT NULL,
  `image_path` text DEFAULT NULL,
  `address_1` text DEFAULT NULL,
  `address_2` text DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(10,8) DEFAULT NULL,
  `event_by` text DEFAULT NULL,
  `genre` varchar(255) DEFAULT NULL,
  `start_time` timestamp NULL DEFAULT NULL,
  `start_time_not_parsed` datetime DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `duration` time DEFAULT NULL,
  `ticket` text DEFAULT NULL,
  `check_address` varchar(255) DEFAULT NULL,
  `going` text DEFAULT NULL,
  `interested` text DEFAULT NULL,
  `createdAt` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`);

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Did you try to create `events_liked` before `event_details` exists? — Bill Karwin, Mar 12 '21 at 17:11
I also notice `event_details` does not have a PRIMARY KEY constraint on its `id` column. You need that. — Bill Karwin, Mar 12 '21 at 17:12
You might like to read this checklist of foreign key requirements: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Mar 12 '21 at 17:14
Actually ID has unique key defined already. I exported from SQL file and forgot that I altered the table. So here the constraints : ALTER TABLE `event_details` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `url` (`url`); I didn't try to create events_liked before, why ? — yoann84, Mar 12 '21 at 17:15
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) — rkosegi, Mar 12 '21 at 17:17

score 1 · Answer 1 · answered Mar 12 '21 at 19:32

In MariaDB

TEXT and BLOB columns cannot be used as foreign keys

Knowledge Base » MariaDB Server Documentation » High Availability & Performance Tuning » Optimization and Tuning » Optimization and Indexes » Foreign Keys

Possible solution 1: add generated column of VARCHAR(xxx) datatype which is assigned with the prefix of TEXT column then use it for FK.

Possible solution 2: add generated column of proper datatype which is assigned with some hash of TEXT column then use it for FK.

Both solutions assumes that generated column is created in both tables.

Can't create table with foreign key Maria DB

1 Answers1