0
Fixed

Index columns in generated SQL are incorrectly ordered by when they were first input rather than where they are currently located, causing errors

baz 1 year ago updated by Support 1 year ago 4

The SQL that is generated orders columns for indexes by when they were first input into the table, rather than how they currently appear in the diagram. This causes malformed indexes and errors when running the queries. For example, this FK:


KEY `FK_idx_account_profile` (`profileNum`, `accountID`),
CONSTRAINT `FK_account_profile` FOREIGN KEY `FK_account_profile` (`profileNum`, `accountID`) REFERENCES `profile` (`profileNum`, `accountID`)

Should be as specified by the diagram like so:


KEY `FK_idx_account_profile` (`accountID`, `profileNum`),
CONSTRAINT `FK_account_profile` FOREIGN KEY `FK_account_profile` (`accountID`, `profileNum`) REFERENCES `profile` (`accountID`, `profileNum`)

To reproduce:


1. Create a new table with a compound primary key composed of two or more columns

2. Re-order the columns by dragging one above the other

2. Create a second table and a relationship from the first table to the second table

3. Generate SQL


ACTUAL: columns for index appear in the order input, causing a mismatch with primary key

EXPECTED: columns appear in the order currently displayed in diagram, matching primary key


The workaround is to delete the columns from the diagram then re-insert them in the order you would like them to appear in the SQL.

Answer

Answer
Fixed

Dear User,


 We have fixed the issue and released new version of SqlDBM. 


Please follow the following steps to fix the issue in your database model:


  • In Parent table, update the PK column/s order OR change any of PK column name to trigger the order fix in "Foreign Key" index. 
  • After this change, you can revert back your change.


Feel free to contact us in case issue still persists.


Again, thank your reporting the issue and using SqlDBM.


- SqlDBM Team

Thank you for feedback, we will review this ticket soon

+1
Accepted

Thank you for bug report, we will fix it.

SqlDBM Team

Answer
Fixed

Dear User,


 We have fixed the issue and released new version of SqlDBM. 


Please follow the following steps to fix the issue in your database model:


  • In Parent table, update the PK column/s order OR change any of PK column name to trigger the order fix in "Foreign Key" index. 
  • After this change, you can revert back your change.


Feel free to contact us in case issue still persists.


Again, thank your reporting the issue and using SqlDBM.


- SqlDBM Team