Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

[MySQL] foreign key not visible

JeAn-PhI's profile image JeAn-PhI posted 1 month ago in General Permalink

hello

foreign keys are no longer visible in the "foreign keys" tab of the table description, nor in the "Code CREATE" tab while a "SHOW CREATE TABLE" query displays them correctly

3 attachment(s):
  • 2022-08-30-12_27_55-localhost_foo_t_child_foo_-HeidiSQL-Portable-12.0.0.6468
  • 2022-08-30-12_28_18-localhost_foo_t_child_foo_-HeidiSQL-Portable-12.0.0.6468
  • 2022-08-30-12_30_41-localhost_foo_t_foo_-HeidiSQL-Portable-12.0.0.6468
ansgar's profile image ansgar posted 1 month ago Permalink

Could you please post the version of HeidiSQL and of the server you are using?

Also, the CREATE code for both t_foo and t_child_foo would be helpful to reproduce.

JeAn-PhI's profile image JeAn-PhI posted 1 month ago Permalink

see attachments

3 attachment(s):
ansgar's profile image ansgar posted 1 month ago Permalink

Thanks for the details. Can confirm that!

Reason is that MySQL 5.0 did not have the REFERENTIAL_CONSTRAINTS view in information_schema. The query for the detection shows an error in the log panel:

SELECT * FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t_child_foo' ORDER BY ORDINAL_POSITION;
SHOW INDEXES FROM `t_child_foo` FROM `test`;
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE   CONSTRAINT_SCHEMA='test'   AND TABLE_NAME='t_child_foo'   AND REFERENCED_TABLE_NAME IS NOT NULL;
/* SQL Error (1109): Unknown table 'referential_constraints' in information_schema */

That view was introduced later (in MySQL 5.1 I think).

I would anyway highly recommend updating MySQL here. MySQL 5.0 is from 2008.

JeAn-PhI's profile image JeAn-PhI posted 1 month ago Permalink

but it worked with previous versions (see attachments) updating the version of MySQL is not an option at the moment

3 attachment(s):
  • 2022-09-02-09_51_00-propos-de-HeidiSQL
  • 2022-09-02-09_51_07-Window
  • 2022-09-02-10_00_33-Window
ansgar's profile image ansgar posted 1 month ago Permalink

Yes, the code in HeidiSQL 10.x could detect these, but not those from newer MySQL versions. Also, the old approach was slower than the current.

Sorry but I don't support such old server versions.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.