Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 29, 2016 10:39 AM
Hi there! I’m just curious how I enforce unique values in a column or a group of columns – that is, how to enforce a unique constraint. So that I don’t, for example, accidentally enter the same person (combination First Name + Last Name) twice.
Apr 29, 2016 02:21 PM
You can add a new field, i’ll call it ID, and give it a formula record_ID(). You’ll find that even for 2 records with the same name+First name, internally, they are different. You can also add an auto numbered field in case you need a more visual and easy content on your layout.
Do you need to enforce unique First Name+Name ? Not always a good thing, not always possible. I remember the time were programmers wrote something like JohnOsborne1, JohnOsborne2, awful !
Hope it helps !
Apr 29, 2016 08:40 PM
Ah, yes – name was a bad example. But there are lots of legitimate times to enforce a unique constraint. So… As I’m understanding, this isn’t possible with AirTable, which means, essentially, it’s not a database, but rather a fancy spreadsheet. More Google Forms than MySQL.
Still, an interesting product – it’ll be interesting to see if it develops into a functional DBMS.
Apr 30, 2016 03:34 AM
Yes, i see!
The Primary Field, the first field in a table, should have the uniqueness you are asking for. I can’t see how to force it in " Customize field type".
Maybe you can apply a formula combining other fields or whatever magic calculation. I didn’t try yet.
Of course Airtable is not Mysql. I use it for what it is, a very good and easy tool for “online tables” for personal use or for a workgroup.
Would you mind posting this feature in the " FEATURE REQUESTS " section ?!
Jul 24, 2019 03:13 PM
Yep - had the same problem. Not really a database if you can’t make the primary field (identifier) unique.
Jul 25, 2019 10:08 AM
You can make the primary field unique. Airtable just doesn’t enforce it, is all. But you can enforce it if you want to.
Airtable does enforce a unique ID for each record, it just isn’t exposed to you as the user unless you dig it up with a formula field and this formula:
RECORD_ID()
You could use Airtable’s unique ID for your record as the Primary field if you want.
You could create an Autonumber field, and then pull the auto-number generated into your Primary field if you want.
You could use the De-Duping block to enforce uniqueness on pretty-printed Primary fields if you want.
There are plenty of options for doing your own enforcing of uniquness on the Primary field.
Jul 25, 2019 10:27 AM
Thank you.
Is there a way to make a text field unique? For instance, if I want to create a short text field for Store Name, I don’t want a user to be able to enter duplicates of that Store name. The field should be unique and not allow a duplicate entry.
Is this possible?
Thanks for your help.
Amy
Jul 25, 2019 06:44 PM
There’s no way to prevent the same store name from being entered twice. When it comes to any manual entry field, Airtable offers no mechanisms that restrict data entry.
If you’re working solely in Airtable, the best you can do is incorporate some (admittedly complex) table mashing to internally track each thing entered into a specific field, and pop a visible indicator into another field if a duplicate is found.
If you’re using an external form system like Jotform for entry, there may be ways to throw up a similar alert during the entry phase, and prevent the form from being sent until it’s corrected. (I’m still coming to grips with Jotform, so I can’t say for sure whether that functionality is possible.)
Jul 26, 2019 08:11 AM
Thank you for your helpful reply, Justin. Much appreciated.
Amy
Aug 14, 2020 07:38 AM
We’ve built this feature in our form to enforce unique values in a field.