Create table with foreign key constraint failed

Question

After starting the verification system on the site, I got an error in the database

MySQL Query Error:

create table `site_checker_b_forum_file` ( 
ID int(18) not null auto_increment,
FORUM_ID int(18) null REFERENCES B_FORUM(ID),
TOPIC_ID int(20) null,
MESSAGE_ID int(20) null,
FILE_ID int(18) not null REFERENCES B_FILE(ID), 
USER_ID int(18) null, 
TIMESTAMP_X timestamp not null, 
HITS int(18) null, primary key (ID), 
index IX_FORUM_FILE_FILE(FILE_ID), 
index IX_FORUM_FILE_FORUM(FORUM_ID), 
index IX_FORUM_FILE_TOPIC(TOPIC_ID), 
index IX_FORUM_FILE_MESSAGE(MESSAGE_ID) 
)

[[1005] Can't create table bitrix.site_checker_b_forum_file (errno: 150 "Foreign key constraint is incorrectly formed")]

Сomplete error code

LATEST FOREIGN KEY ERROR                                                                                                                                                        
------------------------  
Error in foreign key constraint of table `bitrix`.`site_checker_b_forum_file`:                                                               
Create  table `bitrix`.`site_checker_b_forum_file` with foreign key (FORUM_ID) constraint failed. Referenced table `bitrix`.`B_FORUM` not found in the data dictionary.---------
---

But I have the table B_FORUM B_FORUM table

CREATE TABLE `b_forum` (
  `ID` int(18) NOT NULL AUTO_INCREMENT,
  `FORUM_GROUP_ID` int(11) DEFAULT NULL,
  `NAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `DESCRIPTION` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `SORT` int(10) NOT NULL DEFAULT 150,
  `ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_HTML` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ALLOW_ANCHOR` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_BIU` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_IMG` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_VIDEO` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_LIST` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_QUOTE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_CODE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_FONT` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_SMILES` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_UPLOAD` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ALLOW_TABLE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ALLOW_ALIGN` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_UPLOAD_EXT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ALLOW_MOVE_TOPIC` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `ALLOW_TOPIC_TITLED` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ALLOW_NL2BR` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ALLOW_SIGNATURE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `PATH2FORUM_MESSAGE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ASK_GUEST_EMAIL` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `USE_CAPTCHA` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `INDEXATION` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `DEDUPLICATION` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
  `MODERATION` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ORDER_BY` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'P',
  `ORDER_DIRECTION` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'DESC',
  `LID` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ru',
  `TOPICS` int(11) NOT NULL DEFAULT 0,
  `POSTS` int(11) NOT NULL DEFAULT 0,
  `LAST_POSTER_ID` int(11) DEFAULT NULL,
  `LAST_POSTER_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LAST_POST_DATE` datetime DEFAULT NULL,
  `LAST_MESSAGE_ID` bigint(20) DEFAULT NULL,
  `POSTS_UNAPPROVED` int(11) DEFAULT 0,
  `ABS_LAST_POSTER_ID` int(11) DEFAULT NULL,
  `ABS_LAST_POSTER_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ABS_LAST_POST_DATE` datetime DEFAULT NULL,
  `ABS_LAST_MESSAGE_ID` bigint(20) DEFAULT NULL,
  `EVENT1` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'forum',
  `EVENT2` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'message',
  `EVENT3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HTML` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `XML_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_FORUM_SORT` (`SORT`),
  KEY `IX_FORUM_ACTIVE` (`ACTIVE`),
  KEY `IX_FORUM_GROUP_ID` (`FORUM_GROUP_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `b_file` (
  `ID` int(18) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP_X` timestamp /* mariadb-5.3 */ NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `MODULE_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HEIGHT` int(18) DEFAULT NULL,
  `WIDTH` int(18) DEFAULT NULL,
  `FILE_SIZE` bigint(20) DEFAULT NULL,
  `CONTENT_TYPE` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'IMAGE',
  `SUBDIR` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FILE_NAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ORIGINAL_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DESCRIPTION` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HANDLER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EXTERNAL_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_B_FILE_EXTERNAL_ID` (`EXTERNAL_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4951 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Provide DDLs for all 3 mentioned tables (add them for B_FORUM and B_FILE) as formatted code. *But I have the table B_FORUM* - in what scheme? — Akina, Feb 05 '21 at 06:26
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) — Ashish Ratan, Feb 05 '21 at 07:56
When somebody asks you to provide DDL he wants to see complete CREATE TABLE script for a table. You provide it for one table only whereas we need in such scripts for all tables mentioned anywhere. PS. Screenshots and even DESCRIBE outputs are not safe for this purposes. PPS. Also provide precise DBserver version. — Akina, Feb 05 '21 at 10:28
no, i try all, when i use SET FOREIGN_KEY_CHECKS=0 and create the table, the site checker delete the table:( — Karina, Feb 05 '21 at 10:57
Akina i add all the code to the question, thanks:) mysql Ver 15.1 Distrib 10.5.8-MariaDB, for Linux (x86_64) using readline 5.1 — Karina, Feb 05 '21 at 11:09

score 0 · Accepted Answer · answered Feb 05 '21 at 12:00

0

If your db is hosted on a Unix-based system, or if lower_case_table_names is set to 0, MariaDB has case sensitive table names, so you need to use b_forum and b_file, not B_FORUM and B_FILE. See the manual. Your CREATE TABLE statement works fine in this demo if you match the case of the other table declarations.

answered Feb 05 '21 at 12:00

Nick

138,499
22
57
95

Thank you it was really the case sensitive table names, I change it and it create the table:) but after I run the site checker - it delete gives me the same error and delete the table site_checker_b_forum_file – Karina Feb 08 '21 at 08:23
@Karina I'm glad that worked, but it sounds like you have a new problem? If so, please ask a new question. – Nick Feb 08 '21 at 11:12

Create table with foreign key constraint failed

1 Answers1

Linked