nocodb
nocodb copied to clipboard
nocodb
🔦 Feature: Should be able to add unique constraints to columns
Please confirm if feature request does NOT exists already ?
- [X] I confirm there is no existing issue for this
Describe the usecase for the feature
I have a table where i want to ensure the uniqueness of column. If someone is trying to add a single row/bulk add that was already in database they should get the existing values in database in a readable format to copy. Based on that we will delete the duplicate values in new data and upload the data again.
Suggested Solution
Provide the option to enforce uniqueness in the UI in column dropdown when adding a new column/bulk add. if duplicate values are found then can catch the exception and show it in a readable format in a UI model so that we can identify and remove the duplicate values.
Additional Context
No response
I encountered this issue today.
In my NocoDB this option does not appear to prevent duplicate values in the column. Does anyone know how to proceed?
There are no NU,PK,AI,UN,AU options in version 105.3. But in my impression, there are these options in previous versions.
@dstala this may be old news to you but it looks to me like at least part of this issue could be resolved by fixing these two lines, which I think would at least allow unique columns to be identified as such. https://github.com/nocodb/nocodb/blob/d9f7aee12101c1d58ee5f0ca7887ba41ecc2a5c0/packages/nocodb/src/db/sql-client/lib/pg/PgClient.ts#LL849C9-L850C95
NN - Not NULL PK - Primary Key AI - Auto Increment UN - Unsigned AU - Auto Update (Timestamp)
I understood the requirement. Will discuss with team on this.
Versions 0.204.4 and 0.202.6 have been tested and do not take effect. Still reporting an error.
Would also benefit greatly from this feature. It's a standard database feature that many people would expect to exist. We will pay $20 USD in Bitcoin to whoever submits a PR to implement this feature.
Would love to see this for the Cloud version using the default Data Source.
NN - Not NULL PK - Primary Key AI - Auto Increment UN - Unsigned AU - Auto Update (Timestamp)
I understood the requirement. Will discuss with team on this.
@dstala I see that this has been put on hold at the moment. Is it planned to be implemented in a future update?
I would love to see UNIQUE column too. We cannot add additional PK in UI., so please allow us to add UNIQUE constrain. That much needed for any kind CRM. p.s. UN is not Unique, but Unsigned. (enforce non negative)
What I've done is added a constraint in the DB manually. There was a single specific path that worked for me, so here's the full write-up:
- Connect to psql:
docker exec -i your_container -U your_user -W your_db- you may get '$' or just a cursor, depending on your client - List all relations:
\dt *.*- Find your table, make note of the schema - List table detail
\d "your_schema.your_table"- IMPORTAINT! All unquoted names are considered lower-case and won't work with variable-case table names - If you got
ERROR: relation "your_schema.your_table" does not exist, continue, otherwise skip the next step - Run
SET search_path TO your_schema;- Don't forget semicolons! psql returns 'SET' on success; this setting only lasts for the duration of current session - Run
ALTER TABLE "your_table" ADD CONSTRAINT your_constraint_name ("Colum1", "Column2");- Optionally multiple columns, 'ALTER TABLE' returned on success - Congrats! Repeat step 3 to check your new constraint
Notes:
SET search_path TO your_schema,public;did not work for me\dt "your_schema.*"would list the table, but\d "your_schema.your_table"would return error\dn+would show thatyour_schemahas no privileges, but adding those usingGRANT ALL ON SCHEMA your_schema TO your_userdid not help, even though the user was added to privileges - You may need to include this step too if the above doesn't do it- You shouldn't need to select DB, as that's included in step 1, but if you do it's:
\c your_db
Hope that helps
@wingkwong Is there any particular reason this feature was placed indefinitely on hold? What would be the overall difficulty to implement this into the nocodb source.
I switch from Coda in order to have a better database management for this internal app I am doing, and then push the data back in Coda. This is my first database in Noco, it looked good but the only feature I really needed was having the ability to "unique", in order to remove dupplicates. I will need to find an altrenative. It's an important feature!
Don't know about when they will be adding this feature in UI. But we can achieve the same using API request. So rather defining table through UI, you can create table using API which support unique option for field. API Documentation
I would have loved using combination of field as unique, but at least API gives workaround to field specific uniqueness.
Sample unique field creation API sample.
{ "title": "uuid", "type": "ID", "pk":1, "pv": 1, "rqd":1, "unique":1 }
Joking aside, upon table creation via the UI, a hidden column named id is created and is set as a primary key. This field is an integer and is populated with unique integer values. Here is the formula in our postgres backend: nextval(‘‘8dhqp380hM083e3.“table_name_id_seq”’::regclass).
Let's say we create a new column named uuid as a pk. The id column will remain a pk, so now our pk columns are id and uuid. Since the id column gives each row a unique identifier, this means we can add duplicate values to our uuid and the rows will still be accepted since the id column makes it unique by default.
One way we have found to fix this is to delete id via our database backend after the creation of uuid and re add it without making it a pk. After this change, if you try to add duplicate uuid values into your table the frontend will correctly tell you that "this row already exists".
However this makes the front-end pk feature useless for less technical users.
Come on, devs. It's been 4 years since this issue appeared... WE WANT TO BE NOTICED!
