Hi all,
Is there a way to index columns on the database? I am having problems on keeping records unique. Can someone help out?
Thanks in advance.
Hi, @Ramon_Reyes
Sure, you can add constraints to the column in the Schema → Table Editor tab:
Regards,
Marina
That’s true. But how do I add constraints on multiple columns? Something like:
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
What about adding constraints to each column that you need? Or maybe I’m missing something? If so, please explain your case so we can help you.
Regards,
Marina
By adding “adding constraints to each column that you need”, won’t that create like several indices? I’d like to implement 1 unique index on several columns – like this SQL: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,…);
Unfortunately, you cannot group multiple columns into a single index.
May I ask what database is used in Backendless?
We have a combination of database technologies depending on a scenario used by the application.
My client knows tech and they would like to know what the database is, or if that engine can take on future access rules our apps might require. As it is, the single column index falls short of keeping our records unique. Some may argue about the need to optimize but the reason ANSI SQL exists is to enforce uniqueness through multi-column index. We are working on a work-around hence the question about what your database is. I guess we as your client-developer have to know what the limitations are moving forward.
We do not provide direct access to the database. The only way to interact with data stored in Backendless is via the APIs we publish. As a result, the question of what the underlying database is would not be productive.
I understand you need a combination of fields in a record to be unique and that is a reasonable request which we will consider. However, at the present moment, there are no APIs or management facilities exposed to the users to enable that (even if the underlying database technology supports it).
Thanks for your response.
The reason we are discussing this is because of yesterday’s erratic behavior of backendless caused some duplication in the DB.
One page failed to load all the resource needed. The remote user did not take a screenshot but reported that the page load was faulty. That page’s only function was to insert one record and load a new and different page. However, in the end 4 identical records were inserted. I figured that would not happened if a more robust index was enforced.
Please consider the following workaround: create a new generated column that concatenates the values (or generates a hash from the values) that should be unique when combined. After that assign a unique constraint on that generated column.
Do you know how to create generated columns in Backendless?
Thank you for your suggestion. I have thought of that but as a column index and not generated. But I will definitely try it at a later time. We have resolved the weakness programmatically, albeit untested because it is almost impossible to recreate yesterday’s event.