Foreign keys display issue

existing foreign key does not display on the list. it having issue on this latest version 10.3.0.5858

Which server system?

My server ( mysql server ) it display empty as long as the table created foreign key.

before that, i can see the foreign key

Ok, then what MySQL version is it? Please be more detailed. Probably you should also post the result of SHOW CREATE TABLE mytable so I can make a deeper analysis.

CREATE TABLE usergroups ( UserID varchar(6) NOT NULL, GroupID int(11) NOT NULL, PRIMARY KEY (UserID,GroupID), KEY UserID (UserID), KEY GroupID (GroupID), CONSTRAINT GroupID FOREIGN KEY (GroupID) REFERENCES modulegroups (GroupID), CONSTRAINT UserID FOREIGN KEY (UserID) REFERENCES userpassword (UserID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

I use Mysql 5.0 only , stable version still able to see the foreign key after i update nightly build then problem already.

hi, i'm facing the same issue with mysql 4 & mysql 5... MySQL 8 nno issue for me.

i'm using the latest Heidi 11.0.0.5931 as of today.

Any workaround?

same problem: existing Foreign keys do not display in Foreign keys tab, even though I just created (more than) one, saving after each one. Using "My Local MariaDB connection" (although I'm an newbie & not really sure why I set it up this way; just trying to get back into SQL after about a year of not messing with it [when I set it up initially]).

(In one case, I was trying to create a foreign key in another table; in another (e.g., screen 2), I was trying to create a self-referencing [same table] foreign key. Both had the same result [no foreign keys displayed in Foreign keys tab).

Attached are screen dumps (1) AFTER I'd created a foreign key (note that NO foreign keys are listed); (2) a screen just before I save a new foreign key that I've just created (all drop-downs for Columns, Ref table, etc. were auto-populated correctly & I chose from checkboxes [i.e., I didn't type any of the names]); and (3) the screen just after I clicked "Save" on screen 2 (foreign key definitions disappeared [again]).

I tried all this in HeidiSQL 10.3, and then reverted to 10.2 to try again. Same results. (For what it's worth, I'm on Win10/Pro64.)

(See also below for all relevant SQL code I could find.)

"???????"

Any help would be much appreciated! Here's the CREATE code for 2 tables for which I've attempted to create foreign keys:

CREATE TABLE ptplanttaxon ( PTTaxonName VARCHAR(120) NOT NULL COLLATE 'ascii_general_ci', PTTaxonRank VARCHAR(50) NOT NULL COLLATE 'ascii_general_ci', PTTaxonParent VARCHAR(120) NOT NULL COLLATE 'ascii_general_ci', PTTaxonDescription VARCHAR(120) NOT NULL COLLATE 'ascii_general_ci', PRIMARY KEY (PTTaxonName) USING BTREE, UNIQUE INDEX PTTaxonParent (PTTaxonParent) USING BTREE, UNIQUE INDEX PTTaxonRank (PTTaxonRank) USING BTREE ) COMMENT='PT\'s plant taxa: info about each taxon in which PT is interested' COLLATE='ascii_general_ci' ENGINE=MyISAM ;

CREATE TABLE rank ( RankRank VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'ascii_general_ci', RankParentRank VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'ascii_general_ci', PRIMARY KEY (RankRank) USING BTREE, INDEX RankParentRank-Rank (RankParentRank) USING BTREE ) COMMENT='Taxonomic ranks (e.g., "genus", "subspecies")' COLLATE='ascii_general_ci' ENGINE=MyISAM ;

...and here's all the code that was at the bottom of my HeidiSQL GUI:

/ Delimiter changed to ; / / Connecting to 127.0.0.1 via MariaDB or MySQL (TCP/IP), username root, using password: No ... / SELECT CONNECTION_ID(); / Characterset: utf8mb4 / SHOW /!50002 GLOBAL / STATUS; SELECT NOW(); SHOW VARIABLES; / Connected. Thread-ID: 15 / SHOW TABLES FROM information_schema; SHOW DATABASES; / Entering session "My Local MariaDB connection" / USE ptplanttaxa; SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='ptplanttaxa'; SHOW TABLE STATUS FROM ptplanttaxa; SHOW FUNCTION STATUS WHERE Db='ptplanttaxa'; SHOW PROCEDURE STATUS WHERE Db='ptplanttaxa'; SHOW TRIGGERS FROM ptplanttaxa; SELECT , EVENT_SCHEMA AS Db, EVENT_NAME AS Name FROM information_schema.EVENTS WHERE EVENT_SCHEMA='ptplanttaxa'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM rank FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; SHOW ENGINES; SHOW COLLATION; SHOW CREATE TABLE ptplanttaxa.rank; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank'; / #1634496361: Access violation at address 0000000000700A51 in module 'heidisql.exe'. Read of address FFFFFFFFFFFFFFFF Message CharCode:9 Msg:256 / SELECT 1 FROM rank LIMIT 1; ALTER TABLE rank ADD CONSTRAINT RankParentRank-Rank FOREIGN KEY (RankParentRank) REFERENCES rank (RankRank); SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='ptplanttaxa'; SHOW TABLE STATUS FROM ptplanttaxa; SHOW FUNCTION STATUS WHERE Db='ptplanttaxa'; SHOW PROCEDURE STATUS WHERE Db='ptplanttaxa'; SHOW TRIGGERS FROM ptplanttaxa; SELECT , EVENT_SCHEMA AS Db, EVENT_NAME AS Name FROM information_schema.EVENTS WHERE EVENT_SCHEMA='ptplanttaxa'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM rank FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; / Entering session "My Local MariaDB connection" / SHOW CREATE TABLE ptplanttaxa.rank; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM ptplanttaxon FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' AND REFERENCED_TABLE_NAME IS NOT NULL; SHOW CREATE TABLE ptplanttaxa.ptplanttaxon; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon'; SELECT 1 FROM ptplanttaxon LIMIT 1; ALTER TABLE ptplanttaxon ADD CONSTRAINT PTTaxonParent-PTTaxonName FOREIGN KEY (PTTaxonParent) REFERENCES ptplanttaxon (PTTaxonName); SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='ptplanttaxa'; SHOW TABLE STATUS FROM ptplanttaxa; SHOW FUNCTION STATUS WHERE Db='ptplanttaxa'; SHOW PROCEDURE STATUS WHERE Db='ptplanttaxa'; SHOW TRIGGERS FROM ptplanttaxa; SELECT , EVENT_SCHEMA AS Db, EVENT_NAME AS Name FROM information_schema.EVENTS WHERE EVENT_SCHEMA='ptplanttaxa'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM ptplanttaxon FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' AND REFERENCED_TABLE_NAME IS NOT NULL; / Entering session "My Local MariaDB connection" / SHOW CREATE TABLE ptplanttaxa.ptplanttaxon; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon'; / #1634496361: Access violation at address 0000005072F00000 in module 'heidisql.exe'. Execution of address 0000005072F00000 Message CharCode:13 Msg:256 / SELECT 1 FROM rank LIMIT 1; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' ORDER BY ORDINAL_POSITION; ALTER TABLE ptplanttaxon ADD CONSTRAINT PTTaxonRank-Rank FOREIGN KEY (PTTaxonRank) REFERENCES rank (RankRank); SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='ptplanttaxa'; SHOW TABLE STATUS FROM ptplanttaxa; SHOW FUNCTION STATUS WHERE Db='ptplanttaxa'; SHOW PROCEDURE STATUS WHERE Db='ptplanttaxa'; SHOW TRIGGERS FROM ptplanttaxa; SELECT , EVENT_SCHEMA AS Db, EVENT_NAME AS Name FROM information_schema.EVENTS WHERE EVENT_SCHEMA='ptplanttaxa'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM ptplanttaxon FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon' AND REFERENCED_TABLE_NAME IS NOT NULL; / Entering session "My Local MariaDB connection" / SHOW CREATE TABLE ptplanttaxa.ptplanttaxon; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='ptplanttaxon'; HELP 'CONTENTS'; USE ptplanttaxa; SHOW CREATE TABLE ptplanttaxa.ptplanttaxon; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM rank FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; SHOW CREATE TABLE ptplanttaxa.rank; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank'; SELECT 1 FROM rank LIMIT 1; ALTER TABLE rank ADD CONSTRAINT RankParentRank-Rank FOREIGN KEY (RankParentRank) REFERENCES rank (RankRank); SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='ptplanttaxa'; SHOW TABLE STATUS FROM ptplanttaxa; SHOW FUNCTION STATUS WHERE Db='ptplanttaxa'; SHOW PROCEDURE STATUS WHERE Db='ptplanttaxa'; SHOW TRIGGERS FROM ptplanttaxa; SELECT , EVENT_SCHEMA AS Db, EVENT_NAME AS Name FROM information_schema.EVENTS WHERE EVENT_SCHEMA='ptplanttaxa'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM rank FROM ptplanttaxa; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank' AND REFERENCED_TABLE_NAME IS NOT NULL; / Entering session "My Local MariaDB connection" / SHOW CREATE TABLE ptplanttaxa.rank; SELECT FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='ptplanttaxa' AND TABLE_NAME='rank';

Wow. Was just reading through another thread ("FK keys - cannot add - BUG") and noticed that someone else had this same problem, but they actually knew what they were doing and sorted out their own problem (which was also my problem, but I had/have no clue about what it really means): "tables were created in MyISAM mode not InnoDB."

BUG!!! What my "Wow" is about is that "REALLY? Wow... NO ERROR MESSAGE IS DISPLAYED when a foreign key is attempted to be created when the MODE does not support the option. Actually, then entire "FOREIGN KEY" tab should be grayed out with an explanatory message displayed in the info area, like: "Foreign keys are not supported by xxxxxx mode."

What do I need to do to have this reported/acted on as a bug?

(...and what other info do I need to include for developers when reporting this? [realizing that at this point I don't even know what a "mode" IS, much less what it does and why someone would choose one over another]

Thanks!!

philiptdotcom Thanks!

There was such a warning in older versions, up to v10.1:

I removed that warning about unsupported foreign keys 3 years ago, for issue #375 (commit:59ea094c25e03c86e5b30c87e51fed6dab5ba2a2 ). The server returns an empty engine clause in many cases, especially if InnoDB is the default table engine, and when the server runs in ANSI mode. So the detection in HeidiSQL was very unreliable and displayed that warning although foreign keys were supported.

Unfortunately, newer servers even don't throw an error when you try to create a foreign key in a MyISAM table. The key is silently not added, so this makes the situation even worse.

On the other hand, MyISAM engine is outdated. You are running a quite recent version of MariaDB (10.4) but still use that old table type. You should just select "InnoDB" in the options for that table instead, which magically fixes your foreign key issue.

InnoDB is the default table type since 2010.