Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,300,789
Community Members
 
Community Events
165
Community Groups

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

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?

1 answer

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. 

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

Maybe the UUID function will do for the case?

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

Thu 2-1.png

This works. Thanks!

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

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.

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

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).

Yes this is what I need. 

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

Yes, you are right:

Fri 2-1.png

SELECT DISTINCT * FROM T*

Fri 2-2.png

The duplicate rows will be dropped.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

388 views 2 13
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you