#400 foreign keys

[Anonymous]

Originally created by: a...@anse.de
Originally owned by: a...@anse.de

Reported as bug #1592291 by nobody@sf on 2006-11-07 14:24

When will you have support for creating foreign keys
through the "add field" gui?

[Anonymous]

Originally posted by: rosenfie...@gmail.com

(No comment was entered for this change.)

Labels: -priority-default Severity-Default

[Anonymous]

Originally posted by: a...@anse.de

(No comment was entered for this change.)

Labels: -migrated

[Anonymous]

Originally posted by: gabriel....@gmail.com

This would be a very big plus.

[Anonymous]

Originally posted by: shals_sm...@yahoo.com

Can we add foreign key to a table. If so how??

[Anonymous]

Originally posted by: a...@anse.de

Well that's the issue here - not possible via GUI currently. You can use the query
tab to compose the SQL by yourself after reading the docs about foreign keys:
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Related to issue #436.

[Anonymous]

Originally posted by: a...@anse.de

Issue 948 has been merged into this issue.

[Anonymous]

Originally posted by: a...@anse.de

(No comment was entered for this change.)

Blockedon: 436
Status:

[Anonymous]

Originally posted by: rosenfie...@gmail.com

(No comment was entered for this change.)

Status: Accepted

[Anonymous]

Originally posted by: a...@anse.de

16 stars here, so this will be my next job. I think I'll add a new tab next to the
"Indexes" tab and implement the GUI similar to how it is in MySQL Query Browser.
Any comments or suggestions here?

[Anonymous]

Originally posted by: big...@gmail.com

I don't know how MySQL Query browser does it, but the windows version of navicat's is
quite good and pretty intuitive.

[Anonymous]

Originally posted by: a...@anse.de

Just compared MySQL Query Browser to Navicat's foreign key GUI and decided that
Navicat looks slightly simpler - a flat table view with 6 columns. See attached
design time screenshot.

[Anonymous]

Originally posted by: bas...@hotmail.com

Great to see this functionality coming to HeidiSQL!

While adding it, please also add some simple checks to avoid the very cryptic Mysql
error messages regarding foreign keys. For example, when one tries to add a foreign
key to a column with non-matching data types the Mysql error message is:

Can't create table '.\{databasename}\#sql-60c_c5ac.frm' (errno: 150)

While - in my opinion - it should read: 'Error, can't create foreign key between data
type 'bigint' and 'string'

[Anonymous]

Originally posted by: a...@anse.de

The mentioned error message ("Cab't create table...") is indeed weird. However, if
you lookup the code with
  "path-to-mysql\bin\perror.exe 150"
you will see:
  "MySQL error code 150: Foreign key constraint is incorrectly formed"

I don't think HeidiSQL can check all kinds of restrictions in this case only to
display a nicer message. But probably we can "translate" the message internally to
what perror.exe says, that would help a lot I guess.

[Anonymous]

Originally posted by: a...@anse.de

This issue was closed by revision [r2574].

Status: Fixed

[Anonymous]

Originally posted by: a...@anse.de

Foreign key editor implemented in the table editor as new tab.

Still needs some enhancements:
- Map cryptic error codes to their error message (probably enhances other places as well)
- Disable the new tab or at least the list if the edited table is not InnoDB
- Bug: The columns list in the table editor seem to detect constraints as columns,
most probably a regular expression has to be adjusted here.

[Anonymous]

Originally posted by: Invisibl...@gmail.com

This is already quite usable but it lacks the ability to create, show and edit FKs
targeting tables in foreign databases (schemas). MySQL Query Browser / Administrator
for instance only shows the tables of the current database in the dropdown but allows
to enter (and correctly prefills for existing FKs) a custom string like
"otherdatabase.othertable".

[Anonymous]

Originally posted by: a...@anse.de

Tables from other dbs can be set since [r2586] - combobox allows custom text now.

[Anonymous]

Originally posted by: a...@anse.de

Foreign key editor is now disabled if engine is not InnoDB. The bug in the columns
list is also fixed.

[Anonymous]

Originally posted by: a...@anse.de

[r2593] implements a static mapping list for error codes to messages, returned by
perror.exe . The more meaningful message is appended to the one returned by the
server. For example:

"SQL Error: Can't create table 'test3.#sql-b60_103' (errno: 150)

Foreign key constraint is incorrectly formed."

[Anonymous]

Originally posted by: bas...@hotmail.com

Thanks for adding the readable errormessages

[Anonymous]

Originally posted by: tomas.fe...@gmail.com

I came here while googling this error. This error can still happen (it's not a bug). Double check that BOTH your tables are InnoDB. I accidentally had one MyISAM.

[Anonymous]

Originally posted by: rschu...@gorges.us

I also arrived here googling this error. Also check that both your columns (in each table) are of the exact same type - for example, even if both columns are integer columns, if one is signed, and the other is unsigned, you will get this error.

This really needs better error messages - at least, Heidi could check for these two common conditions (mismatched engine type and mismatched key type) and report either (or both) in helpful language.

[Anonymous]

Originally posted by: a...@anse.de

Sigh... the message from the server could really be more helpful here. If HeidiSQL should take care of that, then this should be done while or after selecting the foreign column. Probably I can add some error popup when pressing the ok button to the foreign column popup.

Any suggestions for phrasing this error? My idea:

"The selected foreign column do not exactly match the source columns data type. This will give you an error message when trying to save this change.
Source: id INT UNSIGNED NOT NULL
Foreign: id INT NOT NULL"

[Anonymous]

Originally posted by: a...@anse.de

Oh, simpler: I should only provide matching data types in that drop down. Just like I recently restricted reference tables to InnoDB tables. Or?

[Anonymous]

Originally posted by: a...@anse.de

[r4325] introduces the mentioned error message when leaving the foreign key editor, when data types or unsigned flags do not match between source and reference columns. Please test. That was quite quick and dirty between dinner and bringing my kids to bed.