PrimaryKey UUID - how to use as a Reference (ForeignKey) in SubTable in Database Manager

[guptast]

Edit: NodejS, MacOS, maria db

I have created two tables in the database manager: main table - users, subtable - user_details

In the users table, I have setup user_id with datatype UUID. In the user_details sub-table, user_id field has been created as a Reference (FK) and linked to user_id field in the users main table (created as a subtable in the database manager).

When a record is inserted in the users table, user_id field is correctly generating a UUID. However, when I try to insert a record in the user_details table and use user_id UUID as generated in the previous insert step, the value being inserted is a random value instead of the actual UUID.

How do I insert the correct value of user_id in the user_details table? I may have missed a step when setting up main & sub tables in the database manager correctly or there may be a different requirement when using the Reference links with a UUID datatype. Any help to resolve this issue is greatly appreciated.

[George]

It looks like your user_details.user_id is not of the uuid type but regular integer.

Maybe this is because it was created as reference some time ago and just recently we improved the reference creation to get the data type they are pointing to automatically on creation.

[guptast]

I created both main and sub tables today in v4.9.0. There’s no option to set the datatype when setting up a Reference field, so, the datatype as integer has been automatically setup.

[guptast]

I just checked the datatype in an external database manager, datatype for the Reference field user_id is shown correctly as char(36).

[George]

Then it should be fine indeed. Maybe refresh the schema again on the insert action dialog and read the column there - it should come as text and not number

[guptast]

I have tried it a few times, removed it from the insert action, refreshed the schema, but it keeps reading the datatype as number.

[George]

Sorry found out that it is a problem in the Query Builder dialog - it always converts references to numbers.

Will fix it in the next update

[George]

Btw it might be stored all fine in your database - maybe just the table editor in tabs has it wrong.
So check the output from the server action

[guptast]

Thanks @George for looking in to this issue and fixing it in the upcoming update.

@George

maybe just the table editor in tabs has it wrong. So check the output from the server action

The server action output is showing the value correctly. The table editor is showing it wrongly as well, with some random values every time a record is added.

[Teodor]

This has been fixed in Wappler 4.9.1