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.

FK keys - cannot add - BUG

Kcko's profile image Kcko posted 1 month ago in General Permalink

Hi, in two last releases is probably bug.

Im trying to add new foreign key, after save button all changes are discarded (nothing happened). See short video: https://bit.ly/318x02o

MySQL 5.6.

Before these releases its worked fine.

Kcko's profile image Kcko posted 1 month ago Permalink

Im trying to create FK with plain SQL

ALTER TABLE pp_forum_post ADD CONSTRAINT fk_forum FOREIGN KEY (pp_forum_id) REFERENCES pp_forum(id) ON DELETE RESTRICT ON UPDATE CASCADE

with same result :/

ansgar's profile image ansgar posted 1 month ago Permalink

Probably the SQL is executed fine, but the table designer just not displays the new foreign key?

Check with such a query:

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE   CONSTRAINT_SCHEMA='mydatabase'   AND TABLE_NAME='mytable'   AND REFERENCED_TABLE_NAME IS NOT NULL;
Kcko's profile image Kcko posted 1 month ago Permalink

I dont think so. If it worked, another attempt to add the same FK would end with some MySQL error, right?

Your query returns empty set :/

ansgar's profile image ansgar posted 1 month ago Permalink

I just booted the MySQL 5.6.47 here, and I could successfully create a foreign key:

ALTER TABLE `fkey`
    ADD CONSTRAINT `FK_fkey_pk` FOREIGN KEY (`Column 1`) REFERENCES `pk` (`Column 1`) ON UPDATE CASCADE ON DELETE SET NULL;

The above query on IS.REFERENTIAL_CONSTRAINTS shows me the key:

CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
def test FK_fkey_pk def test PRIMARY NONE CASCADE SET NULL fkey pk

The table editor also shows that key:

Description

Kcko's profile image Kcko posted 1 month ago Permalink

Hi ansgar, im really sorry, its my f**** fault, tables were created in MyISAM mode not InnoDB.

I completely overlooked it and looked for the problem elsewhere, sorry again :(

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.