On 15 Feb 2018, at 7:19pm, Michael Tiernan <michael.tier...@gmail.com> wrote:
> This might be a general RDBMS question but since I'm using sqlite
> specifically, I hope it passes basic relevancy tests.
I don't think we have any problem answering SQL questions here, as long as
they're good ones.
> I have a table defined as:
>
> CREATE TABLE "CPUModelDictionary" (
> `vendor_id` TEXT,
Make that TEXT COLLATE NOCASE. It will save time and programming when you want
to do searches and sorts.
> `cpu_model_name` TEXT,
and that one too.
> The five fields used in the index create a unique identifier.
Then do CREATE UNIQUE INDEX. That way SQL will enforce the uniqueness.
> I want to reference these rows via a single reference field but I'm not sure
> if there's a "smarter" way to do it. I considered just creating a field
> that's a concatenation of the five fields and using that as the unique link
> to the rows but I'm sure that it's not the best way to do it.
I agree with Peter Da Silva. Declare the unique rowid column for the
"CPUModelDictionary" table:
CREATE TABLE "CPUModelDictionary" (
`rowid` INTEGER PRIMARY KEY,
`vendor_id` TEXT COLLATE NOCASE,
`cpu_family` INTEGER, etc.
From that point onwards you have a unique number which refers to each row in
the table. When you want to refer to a specific combination of characteristics
you can use that single integer. This is called "normalisation" and is a
standard way to do things in SQL.
By the way, your use of backticks to identify column names is not wrong, but it
is rarely done these days and can lead to problems because strings in SQLite
are delimited with apostrophes 'string' which look almost the same. Most
SQLite programmers use column names without any surrounding characters.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
