MySQL Bugs: #23693: Foreign key constraint againts inexistent table won't show meaningful error

Bug #23693	Foreign key constraint againts inexistent table won't show meaningful error
Submitted:	26 Oct 2006 22:01	Modified:	4 Sep 2019 18:54
Reporter:	Antonio Ognio	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:		OS:	Any (Any)
Assigned to:		CPU Architecture:	Any
Tags:	constraint, error, foreign key

Description:
If you fail to provide the name of an existing table in a foreign key contraint you get a generic 150 error witout getting any clue about the reason of the failure being the inexistant table.

How to repeat:
Simply create a table pointing to an inexistant table, e.g. 

CREATE TABLE finished_product_box (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  material ENUM('CARDBOARD','WOOD','PLASTIC') NOT NULL,
  weight NUMERIC(3,2) NOT NULL,
  PRIMARY KEY (id),
  INDEX finnished_product_name_idx (name),
  INDEX finnished_product_variety_idx (material),
  INDEX finnished_product_weight_idx (weight)
) Type=InnoDB;

CREATE TABLE finished_product_box_i18n (
  finished_product_box_id INTEGER NOT NULL,
  culture_id CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  created_by INTEGER NOT NULL,
  updated_by INTEGER NULL,
  PRIMARY KEY (finished_product_box_id, culture_id),
  CONSTRAINT finished_product_box_i18n_box_fk FOREIGN KEY (finished_product_box_id) REFERENCES finnished_product_box (id) ON DELETE RESTRICT
) Type=InnoDB;

# Here i'm referencing the table finnished_product_box because of a typo. The actual name is "finished_product_name". 

Suggested fix:
The code is actually detecting the missing table with the name "finnished_product_table". Why not just spell it out so I go check why that table doesn't exist and I have a great chance of spotting the misspelling quickly.

Thank you for a reasonable feature request.

Bug #26848 is a duplicate of this one.

Dmitri, could you please go over all feature requests in foreign keys and close those that are fixed by WL#148?

Posted by developer:
 
Hello!

The error message which is emitted when we attempt to
create a foreign key referencing non-existing table has
been improved as part of fix for bug#25722927 "NEWDD FK:
ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT"
which was published in 8.0.14.

The new error message which is emitted in this case looks like:
ERROR 1824 (HY000): Failed to open the referenced table 'no_such_table'

Moving this request to Documenting state to let Documentation Team
to decide if any documentation/release notes need to be updated.

Posted by developer:
 
Addressed by the fix for Bug#25722927. The changelog entry for Bug#25722927 (8.0.14) was revised to also mention the fix for this bug report:

A check that ensures compatibility of referencing and referenced column
types in a foreign key definition was moved from the storage engine layer
to the SQL layer. In addition, a better error message is produced when
columns are not compatible, and when a foreign key constraint references a
table that does not exist.