How can I add a primary key to a table on confluence using table transformer macro?

[Kuber V]

I have a table with columns Client, vendor, vendor 2. How can I make the Client column unique by making it primary key.

Can I use alter table command inside table transformer macro?

[Katerina Kovriga _Stiltsoft_]

Hi @Kuber V ,

Please clarify your case for me to check - you want the Client column to be unique without any duplicates? To serve like a kind of UID for each table entry?

Maybe we can just add the number of the current row to the contents of each cell in the Client column?

For example, you have John Smith both in the second and in the forth rows of your table, but they are different people and belong to different vendors. So, we'll rename them as John Smith_2 and John Smith_4. 

[Kuber V]

you want the Client column to be unique without any duplicates? To serve like a kind of UID for each table entry?

- yes this is exactly what I want. As I'm using this table for other operations with other tables also having the Client column.

I know we can  do something like jhon-smith2, that is what I am doing currently. But this client column is being populated by multiple users and I want a constraint to prevent users from entering duplicate client names

[Katerina Kovriga _Stiltsoft_]

Maybe the UUID function will do for the case?

SELECT UUID(T1.'Col 1') AS 'UID', * FROM T*

[Kuber V]

This works. Thanks!

[Kuber V]

But is there a way to make Client column primary key?

[Katerina Kovriga _Stiltsoft_]

If you mean that the generated uids should be unique, then it seems that the UUID function for the AlaSQL library serves this purpose.

Here is its description: https://www.geeksforgeeks.org/uuid-function-in-mysql/

And you may at first reuse your tables and collect them into one master table with the help of the Table Excerpt/Table Excerpt Include macros and then assign uids for this combined table.

If you mean smth else, I'm not sure - the Table Transformer macro works with tables inside its body, not with all the tables in your Confluence like with a database.

[Katerina Kovriga _Stiltsoft_]

And you may also check the NEWID and GEN_RANDOM_UUID functions, they work pretty similar to the UUID function.

[Katerina Kovriga _Stiltsoft_]

We've read the thread again and it seems that maybe we misunderstood the case: maybe you need not the unique uids for the Client column but to prevent users from entering the same client names?

I mean not to add different uids to John Smith if you have them twice in your table (earlier we assumed that they are different people), but to forbid entering the same name if it is already in the table.

If this is the case, maybe the DISTINCT function will help.

Your users may enter duplicates in the original table.

But this table is wrapped in the Table Transformer macro where we select only unique values for the Client column. The duplicates are dropped.

Then you reuse this modified table with the help of the Table Excerpt/Table Excerpt Include macros and use it on other pages to merge with other tables (you've mentioned that you work with other tables that also contain the Client column).

[Kuber V]

Yes this is what I need. 

So I do something like select distinct * from t* inside the table transformer ?

[Katerina Kovriga _Stiltsoft_]

Yes, you are right:

SELECT DISTINCT * FROM T*

The duplicate rows will be dropped.