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.

Composite Foreign Key columns in wrong order

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

Hi! Got a small problem that I wonder if you can advise with? I'll use an example to try to describe it.

I try to create a composite foreign key in a child table - let's call it distributor. In that table I use two columns town + storename as the FK that references town in one table and storenamein another.

It seems OK, but when I click on the towncolumn in distributor, I am shown storename values in the drop down; and when I click on the storename column, I am shown town values. It should be the other way round. This can be avoided by specifying the correct order of the columns if coding directly in SQL, but I can't seem to find a way to fix it in HeidiSQL.

Can you advise?

ansgar's profile image ansgar posted 1 month ago Permalink

Could you paste the create code for that table here, with the original/wrong order of columns?

Jason210's profile image Jason210 posted 1 month ago Permalink
PRIMARY KEY (`storeNo`, `customerNo`, `dateFrom`),
INDEX `FK_order_room` (`storeNo`, `productNo`),
CONSTRAINT `FK_order_room` FOREIGN KEY (`storeNo`, `productNo`) REFERENCES `room` (`productNo`, `storeNo`)
Jason210's profile image Jason210 posted 1 month ago Permalink

All it is is that productNo and StoreNo are switched. It should look like this:

PRIMARY KEY (`storeNo`, `customerNo`, `dateFrom`),
INDEX `FK_order_room` (`storeNo`, `productNo`),
CONSTRAINT `FK_order_room` FOREIGN KEY (`storeNo`, `productNo`) REFERENCES `room` (`storeNo`, `productNo`)
Jason210's profile image Jason210 posted 1 month ago Permalink

So I'd like to know if it is possible to avoid this happening graphically when using HeidiSQL. Many thanks!

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.