Sqlyog Cant Edit Constraint Relationship Between Multiple Databases

[xtrm]

***SOLVED IN 9.X versions and UP

Hello, I am creating a simple relationship constraint between two tables in two different databases:

– table1 in database1

– test2 in database2

The relation constraint works perfectly, but SQLyog is not able to modify it with the gui option: Foreing Keys, Edit

It executes:

SHOW FULL fields from `database2`.`table1`

It shows an error explaining that the key “database2.table1” doesn't exist.

That message error is true, since table1 is not in database2

Solution:

The bug or request to fix it should be to check for the proper indexes and foreing keys database.

Its taking as default the focused gui database, and adding the prefix of the current database (database2) for tables.

Also the edit relationship GUI doesnt support to Select a table or key from a different database. Only allows to select a table/key from the currect focused database.

Unnafortunately it wont work in a lot of projects with data shared between multiple databases.

Test script:

1. Create a simple table in database1 with a primary key 'id'.

2. Create a second table in database2 with the following SQL query:

CREATE TABLE `test2`


(


`id` bigint(20) NOT NULL AUTO_INCREMENT,


`fk` bigint(20) DEFAULT NULL,


PRIMARY KEY (`id`),


KEY `test2_fk_idx` (`fk`),


CONSTRAINT `test2_fk_table1_id` FOREIGN KEY (`fk`) REFERENCES `database1`.`table1` (`id`)


) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci

This feature is already implemented in Mysql WorkBench Editor, so until a fix is made in SQLYog, we can modify those keys with it.

[Author]

Posts