Says there is no unique data but there was only one duplicate

[SimpleSamples]

Details for the issue

What did you do?

I had a table with data in it that I forgot to set a field to unique, so I tried to modify the table to add unique for the field.

What did you expect to see?

I expected it to just do it. But actually, there was one duplicate.

What did you see instead?

It said there were no unique data and that was not true. When I looked I found one duplicate and deleted one row and then I was able to modify the table.

The message saying there was no unique data was totally wrong. Most of the data was unique.

Useful extra information

DB4S v3.10.100 (Oct 10 2018) on Windows 10 (10.0) (winnt/10.0.17134) [x86_64]
using SQLCipher Version 3.4.1 (based on SQLite 3.15.2)
and Qt 5.8.0

[justinclift]

The message saying there was no unique data was totally wrong. Most of the data was unique.

Ouch, that's not good. We'll probably need to check if that happens in the general case, or if it was something unexpected to do with your database structure or data.

Do you happen to have a copy of the database still around, with the duplicate row that causes the error? Checking just in case we can't duplicate the problem (haven't tried yet), it'll be useful to have some ability to trigger the problem + figure out the cause/solution. 😄

[SimpleSamples]

I have the data but not before I modified it. The data is not for public consumption. It is a very simple table with the three columns User, Priority and Comments, all text.

I can re-create the problem easily. My test DB is:

CREATE TABLE "SomeUniques" (
"User" TEXT,
"Priority" TEXT,
"Comments" TEXT
)

And with some data with one duplicate it complains when I try to add "Unique" using the UI.

[justinclift]

Interesting. Does the same thing happen with the nightly build from a few hours ago?

    https://nightlies.sqlitebrowser.org/latest/

[SimpleSamples]

I installed DB.Browser.for.SQLite-win64.msi from that page. The About box for my DB Browser for SQLite says:

Version 3.11.99 (Dec 13 2018)
Qt Version 5.12.0
SQLite Version 3.26.

I hope that is the version you are hoping for. I get the same message with that.

[SimpleSamples]

DB Browser for SQLite - Album on Imgur

[chrisjlocke]

I get this message when I click the "U" column for the "User" field.

But the data in that column isn't unique - you have two 'User2' records. When you click 'Unique', it means ALL the data in that field for every record has to be unique, ie, no duplicates.
The usual recommended method is to add an 'id' field of an integer field, and set the 'primary key' checkbox. This gives each row a sequential number, which will be unique. This allows your data to have duplicates in other fields, but the 'governing' column is the ID field, making each ROW unique. This allows for relationships then. Another table can reference the ID field in your 'SomeUniques' table.

Lets scrap your table. For explanation purposes. Lets think of a telephone index system. Names in one table, telephone numbers in another.

CREATE TABLE "names" (
"id" INTEGER,
"name" TEXT,
PRIMARY KEY("id")
);

CREATE TABLE "telephoneNumbers" (
"id" INTEGER,
"nameId" INTEGER,
"telephoneNumber" TEXT,
PRIMARY KEY("id")
);

So you might have two entries in the names table:
1,'Chris'
2,'Justin'

and three entries in the numbers table.
1,1,12345
2,2,2468
3,2,36912

A sequential count in the numbers table (1, 2 and 3) points to chris, and two entries for Justin. This means the names can change with no consequences, or the numbers can change - all the 'links' rely on the id numbers.

When I looked I found one duplicate and deleted one row and then I was able to modify the table.

So this is how 'UNIQUE' is meant to work. It creates an index on a field to ensure ALL the data in it is unique. It doesn't just create an index of the unique rows. As soon as you deleted the duplicate row, all the other rows were unique, so the index could be created.

[justinclift]

Looking at that message, the wording could be improved:

Column 'User' has no unique data.
This makes it impossible to set this flag. Please change the table data first.

Something like this would probably be clearer:

Column 'User' has duplicate data.
This makes it impossible to enable the 'Unique' flag. Please remove the duplicate data, which will allow this flag to be enabled.

Thoughts? 😄

[SimpleSamples]

Sorry, I thought I made it clear in my initial post. I said there was only one duplicate. So yes, it is just a matter of wording.

As for my table, the user id is unique. I don't want to support duplicates. I wanted to make the field unique so it would tell me if the user id exists in the table already.

[justinclift]

No worries @SimpleSamples, it's probably just us reading this while being a bit rushed. 😄

My suspicion here is the phrase "has no unique data" is probably a typo from "has non unique data".

Looking through our source code, this seems like it'll be easy to update:

sqlitebrowser/src/EditTableDialog.cpp

Lines 472 to 473 in 1af7b03

	QMessageBox::information(this, qApp->applicationName(), tr("Column '%1' has no unique data.\n").arg(field.name())
	+ tr("This makes it impossible to set this flag. Please change the table data first."));

@SimpleSamples @chrisjlocke @MKleusberg Does my suggested wording sound reasonable to you? If so, we can change it right away.

Translations of the updated strings won't happen immediately. We do those in a batch just prior to our releases, but the English version would be fixed in our automatic nightly builds from here on. 😄

[SimpleSamples]

It seems very likely it was a typographical error. I agree that changing it to "has non unique data" would work but to be more precise it should be "has non-unique data".

[chrisjlocke]

"Column 'User' has duplicate data" makes more sense than "non unique data".

Sorry, I thought I made it clear in my initial post. I said there was only one duplicate.

If you have any duplicate data, then you do not have unique data. That is where the error dialog is coming from. Are you suggesting that if your table has one duplicate record, it should still have allowed you to create the unique flag? I get the fact the error could be better worded, but I'm not sure why this issue was raised.

[SimpleSamples]

You are misquoting what was said then using that as proof. It says no unique data and that is wrong.

No, I am not saying the unique option should have been allowed. You are making this into something hundreds of times bigger than it needs to be.

I am sorry for the trouble but this thing exploded into something bigger than it needed to be.

[justinclift]

Sorry, that's on us not you. We often discuss stuff like this - which seems simple - in order to make sure we're really grokking it before changing it. Sometimes (not always) it can seem like we're overcomplicating stuff though. 😇

What I think @chrisjlocke is asking, and I'm also curious about, is doesn't:

Column 'User' has duplicate data.

and:

Column 'User' has non unique data.

... mean the same thing? But with "has duplicate data" being somewhat a more standard phrase people would generally see?

That being said, I'm ok with just fixing the missing "n" typo letter. Was just trying to improve the wording at the same time. 😄

[fomoruto]

I vote for "has duplicate data..." Regards 👍

[justinclift]

Thanks @fomoruto. 😄