List Foreign keys that points *toward* a table

Hi,

we have a wonderful tab in HeidiSQL showing all foreign keys a table has.

Could you add another tab showing all the foreign keys that point towards a table? At least, this tab would show FK that points to the current table and that comes from tables in the current database. At average, FK from tables in the already-opened databases. At top most awesomeness, from all the databases for the current connexion.

I highly guess that would confuse many users, don't you think so? How should the tab be labelled in your opinion?

Something like "Keys from other tables" or "Incoming keys" or "Incoming references".

Otherwise, maybe you could insert them in the current "Foreign Keys" tab, with a new column telling the "source table", like:

Key name | Source table | Source columns | Referenced table | Foreign Columns | ON UPDATE | ON DELETE

Source table would be the table where the key is defined (currently, it's always the table you have selected). That way could be less confusing?

Hm, these rows should be non-editable somehow, not sure if that can be done. However, still sounds a bit confusing from the usability aspect.

It can be useful when you have two tables A and B (feeded with data), with a FK from A.idB to B.id Then you drop the column id in B (with foreign key checks disabled). So you have a FK from A that points to the unexisting column B.id

Now, when you try to create the B.id column again, it will fail because the FK won't validate (your B.id column is empty while A.idB is not).

That happened few days ago for one of my work colleague.

If you dislike adding them in the "Foreign Key" tab, then what about a button stated "Show incoming keys" that would simply trigger a query in a new query tab, retrieving the FK list that points toward the current table and showing the result in the query result section? That would be enough.

Example of such query for MySQL:

SELECT 
ku.CONSTRAINT_NAME AS "Foreign key",
CONCAT("`", ku.TABLE_SCHEMA, "`.`", ku.TABLE_NAME, "`") AS "In",
GROUP_CONCAT(ku.COLUMN_NAME) AS "Source column",
CONCAT("`", ku.REFERENCED_TABLE_SCHEMA, "`.`", ku.REFERENCED_TABLE_NAME, "`") AS "References",
GROUP_CONCAT(ku.REFERENCED_COLUMN_NAME) AS "Target column"

FROM information_schema.KEY_COLUMN_USAGE AS ku
WHERE ku.REFERENCED_TABLE_SCHEMA = '[THE_CURRENT_SELECTED_SCHEMA_NAME]'
AND ku.REFERENCED_TABLE_NAME = '[THE_CURRENT_SELECTED_TABLE_NAME]'
GROUP BY ku.CONSTRAINT_NAME
HAVING `In` != `References`

Result:

Foreign key; In; Source column; References; Target column
FK_map_element_isometric_map_element_country; `eclerd_earthprime`.`map_element_isometric`; id_map_element_country; `eclerd_earthprime`.`map_element_country`; id

Without the HAVING, we could get all the foreign keys (but those defined in the current selected table are already listed in the "Foreign Key" tab, so these rows are quiet useless here).

[I hope that post won't crask up the forum's display...]

+1 for this feature request!

MySQL Workbench lists all foreign keys in a single table, both pointing from and to the selected table. It would be super useful if HeidiSql could do the same. Any chance this will be implemented in the future?

I'm using this snippet for analyzing FKs:

/* find all columns with foreign keys pointing to a specific target column */

SET @TARGET_SCHEMA  = 'my_schema';
SET @TARGET_TABLE   = 'my_table';
SET @TARGET_COLUMN  = 'my_column';



SELECT      kcu.TABLE_SCHEMA                AS SOURCE_SCHEMA,
            kcu.TABLE_NAME                  AS SOURCE_TABLE,
            kcu.COLUMN_NAME                 AS SOURCE_COLUMN,

            kcu.CONSTRAINT_NAME             AS CONSTRAINT_NAME,
            rc.UPDATE_RULE                  AS ON_UPDATE,
            rc.DELETE_RULE                  AS ON_DELETE,

            kcu.REFERENCED_TABLE_SCHEMA     AS TARGET_SCHEMA,
            kcu.REFERENCED_TABLE_NAME       AS TARGET_TABLE,
            kcu.REFERENCED_COLUMN_NAME      AS TARGET_COLUMN


FROM        information_schema.KEY_COLUMN_USAGE         kcu
INNER JOIN  information_schema.REFERENTIAL_CONSTRAINTS  rc      ON (rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA AND rc.TABLE_NAME = kcu.TABLE_NAME AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)

WHERE       kcu.TABLE_SCHEMA            = @TARGET_SCHEMA
AND         kcu.REFERENCED_TABLE_SCHEMA = @TARGET_SCHEMA
AND         kcu.REFERENCED_TABLE_NAME   = @TARGET_TABLE
AND         kcu.REFERENCED_COLUMN_NAME  = @TARGET_COLUMN

ORDER BY    SOURCE_TABLE ASC,
            SOURCE_COLUMN ASC
;

Following on this question

Following on this question

Me too! I really like this idea, because we get in touch with questions like "does some table reference my table" on a regular base?

So I assum no progress has been made on this? This would be a really usefull feature. Not sure why the author thinks this is not a good idea. This would be great for people exploring a database.

@Acs please refer to issue #1825, which I recently accepted as a new feature.

There are numerous cases where such old forum threads were commented. I'm wondering if I should close threads after some years.

@ansgar, please do close them. Or at least put a link to github if the discussion has been moved over there.