Unique ID's (No Duplicates)

[Mcc83]

I'm having some trouble here and wanted to confirm if this was even possible.

I'm working with a dataset that needs a Unique ID that can not exist elsewhere in the column. This will look something like EX00001. I want to make sure new users can not create something with that ID while it is still in that table.

I would like to have some sort of error code that pops up on entering a new form (For other reasons as well outside of this). I spent some time looking for an answer and could not find a solution, is there better terminology I should be searching for?

Thanks!

[ConorWebb96]

Hey @Mcc83,

What data source are you using? Are you using the Budibase internal table or an external data source e.g. Postgres, MySQL etc. The answer will differ depending on what you are using. Its pretty straight forward for external tables, you simply add a primary key unique_id INT PRIMARY KEY and it will handle the rest for you, it won't allow duplicate ids to be entered.

For the internal table we have auto generated fields, but these don't allow for ids to be entered. They simply auto increment the id of the table keep each id unique.

You could do something complicated here and use the public api, along with the execute query action set to a rest endpoint that will check based on the id passed back if there are any rows with the specific id passed back. From here you could then check if any results are returned if there are fail the validation. Then show a message explaining why it failed.

I hope this helps point you in the right direction.

[baruchiro]

Using MySQL and still have a problem with the id column.

CREATE TABLE `lectures` (
  `id` int NOT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `index` int DEFAULT NULL,
  `payment_status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

#15349