Hi there, I am using Google Cloud SQL for m...

(Benoit Gramond) #1

Hi there,

I am using Google Cloud SQL for my app (actually transfer everything from google sheet to Google Cloud SQL) and reorganizing all the data. I have some compulsory things that have been asked for this database: I must use primary keys auto_increment for all my tables.

I have seen in the documentation some explanation about the different ways : https://help.appsheet.com/data/data-integrations/using-data-from-mysql

Everything is well explained but I can not use the RANDBETWEEN() function solution. Indeed, the most of my tables must have increasing IDs over time. Therefore, I imagined another solution:

Put this formula in the initial value: =MAX(table[id_column])+1 Make my id_column KEY HIDDEN REQUIRED in appsheet

This solution seams to be working, but I am asking myself if I missed something. Does anybody already tried this or has an idea about the troubles I might have using this solution?

Thanks a lot

(Dinh Nguyen Nguyen) #2

@Benoit_Gramond Hi Benoit, it is actually not recommended to generate new key values based on the values of existing keys (for example, increment the current maximum key to compute the next key). If your app can work offline, this approach can lead to duplicate keys. For example, 2 different users using the app simultaneously while offline might have the same maximum key value. As a result, when they insert new rows into the app, these rows will have the same key values, which will lead to errors when these rows are synced to the database.

If you absolutely have to have an identity column, or an auto-increment key column in your database, you can work around this by using 2 different key columns. One column will be the actual identity column in the database. The other key column will be the key column in AppSheet. This way, you can mark the identity as not required by the app so that it can be left blank. Each time new rows are inserted into the database, the database will automatically generate new key values for the identity column. As for the AppSheet key column, you can use the UNIQUEID() expression to generate new unique key values for this column. To the database, key values in this column won’t count since it’s not an identity column.

(Benoit Gramond) #3

@Harry this is why I love this app and this community :slight_smile: indeed this is the best solution, it also solve my problem of users adding rows at the same time in my inventory app.

(Benoit Gramond) #4

by the way, if anybody needs to add a column with uniqueid in a table that already had data, here is the SQL code to update this column after creating:

UPDATE table

SET column

= (

SELECT GROUP_CONCAT(SUBSTRING(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’ , 1+ FLOOR(RAND()*LENGTH(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’)) ,1) SEPARATOR ‘’)

FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) as dummy_tbl )

1 Like