Foreign Key creation fails with Error 1170 (Identifier type created as TEXT in MySQL)

Support
1

Hello,

I have a table ‘Province’ with a field named ‘IsoCode’ which is the primary key of data type ‘Identifier’ (Size = 2).

image
image823×436 12.4 KB

I have another table ‘City’ with the field ‘ProvinceIsoCode’ of data type ‘Identifier’ (Size = 2) referring to the IsoCode field of the table Province.

image
image834×503 16.1 KB

I can create a form ‘Cities’ with the field ‘ProvinceIsoCode’ with a Lookup Display Type referring to the form Provinces.

This works fine, but I would like to add a Foreign Key to the City table on the field ProvinceIsoCode with the Data Source Province on the field IsoCode.
I get the following error:
Failed to execute SQL : Error 1170: BLOB/TEXT column ‘ProvinceIsoCode’ used in key specification without a key length

It looks like the physical data type of the field City.ProvinceIsoCode in the underlying MySQL database is being created as a TEXT type instead of a VARCHAR, despite the Five wizard showing ‘Identifier’.

I confirmed this by running the following query in the SQL Editor:

SQL
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ‘ProvinceIsoCode’;
The result shows DATA_TYPE = text and CHARACTER_MAXIMUM_LENGTH = 65535.

It looks like the type is different

image
image1157×381 16.3 KB

image
image1165×373 15.3 KB

Is this a known issue in the development environment, and is there a workaround to force the synchronization?

I attached a sample application as demonstration.

Thank you,

Jean

ForeignKeyTest-20260602-135554073068159.fdf (3.4 MB)

2

Hi Jean,

Thank you for sharing this scenario.

Indeed, behind the scenes, this field definition is considered a text.

I have already notified the development team about this issue. I will keep you updated about any changes.

A workaround solution would be to change both field types (Keys and Foreignkeys) to a GUI with 36 for their sizes, and when recreating the tables, their type would be changed to Char, and the size would be added to it.

Regards,

Elton S

3

Hi Elton,

Thank you for pour answer. The workaround cannot work in my case if I want an ISO code as Primary Key. Changing the type to GUID rejects the 2 letters ISO code.

image
image400×307 12.1 KB

The foreign key is important in my actual application because it links a form and a List page type. This isn’t a blocking issue in my situation, so I’ll wait for the issue to be fixed.

Regards

Jean

4

Finally, this problem is preventing me from using an important feature. I am trying to create a Dependent Lookup (as described here: _Lookup | Five | Low-Code For Real Developers ).

Since it requires a physical Foreign Key between tables for a dependent lookup to work, I cannot filter my dropdowns based on parent fields.

Do you think a solution or a patch for the Identifier/TEXT bug will be provided soon?

Regards,

Jean

5

Hi Jeans,

I am sorry to hear that this issue is affecting our part of the development.

I have notified our development team and escalated this scenario.

As soon as it gets fixed, I will let you know.

Regards,

Elton S

6

Hi Elton,

Thank you very much for escalating the issue.

Regards,
Jean