1-1 UNIQUE Field

Edojay

I have a question regarding the UNIQUE field. Currently, I am using the free version of Xano, so I want to confirm if this feature is available in my plan. In my database, I have two tables - Order and Customer. Each customer is associated with only one order. The Order table contains a reference field (customer_id) linking it to the Customer table. Now, I need to ensure that the customer_id field in the Order table is unique, preventing it from being added to other orders. How can I accomplish this in Xano?

Chris Coleman

Hi, @Edojay. This is possible in Xano (and does not require a paid plan). You can apply a UNIQUE index to that column. Here's some instructions on how to apply an index.

Edojay

Thanks for the quick response Chris! Initially, I tried creating a UNIQUE index while having existing values in the table, which caused an error which I could not figure out:

"SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "mvpw28648_145836_btree_unique_xdo_user_pu_id_asc"
DETAIL: Key (((xdo ->> 'user_pu_id'::text)::bigint))=(0) is duplicated. (SQL: CREATE UNIQUE INDEX "mvpw28648_145836_btree_unique_xdo_user_pu_id_asc" on "mvpw28648_145836" USING btree(((xdo->>'user_pu_id')::bigint) asc))"

Everything is now functioning correctly, thank you for confirming that the functionality works on the free plan!

Chris Coleman

Glad to hear it! That error indicates that there were duplicate values that existed when trying to add the unique index. It's not always obvious especially if you have empty or null values, as these are also technically not unique if you have more than one (even though it appears nothing is there).