distal-attribute
distal-attribute
distal-attribute
distal-attribute

List Foreign keys that points *toward* a table

Xenos posted 1 year ago in Feature discussion

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.

ansgar posted 1 year ago

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

Xenos posted 1 year ago

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?

ansgar posted 1 year ago

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.

Xenos posted 1 year ago

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.

Xenos posted 1 year ago

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...]

stanky posted 2 weeks ago

+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?

apric posted 2 weeks ago

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
;
marianne posted 1 week ago

Following on this question

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