0

Problem Can't create a table with an index column that references multiple rows in a table. Picture example below of what I'm trying to create.

Overview Imagine an (SQLite) table will hold stock dividend payments. The index column is set to the ticker symbols. However, each ticker symbol refers to multiple records, which are organized by a time stamp. The documentation on SQLite and about 15 other tutorials all seem to focus on indexing where there is always a 1:1 relationship between an index and a record. I would like to create an index with a 1:many relationship.

The lookup would find the appropriate stock by symbol, and then (probably) a secondary index on the dates in the first column. But I cannot find any examples where others have tried to set up this structure. Makes me think maybe I don't have the right approach, or this is just a special case.

Example Table Structure

Adestin
  • 153
  • 3
  • 15

1 Answers1

0

I don't think your problem is actually a problem. Putting an index on a column doesn't mean it has to contain unique values. It's perfectly reasonable for values in an indexed column to repeat. Of course there are diminishing returns. E.g. If you have a million rows and only five different values in a column, an index on that column isn't really going to do much for you.

A good rule of thumb is to start with an index on the column(s) you're using in your where clause. Then run the queries and see if you're getting satisfactory performance.

Mike W.
  • 1,345
  • 8
  • 18
  • Thanks for your tips, Michael. I understand creating an index. But I don't understand how to create that index to refer to multiple rows. Could you please provide an SQLite example or a reference to one? Thx! – Adestin Dec 19 '16 at 14:56
  • You're not finding any documentation on that because it's not something you have to do. Just use the standard syntax: create index index_name on table(column(s)). There's nothing special about "referencing multiple rows." It will just work! – Mike W. Dec 19 '16 at 15:21
  • oh. oh. So the primary key fields don't need to be unique...so each field in that column would have repeating keys...that gives me the search results I need. Thanks, Michael. That is exactly what I needed to hear. – Adestin Dec 19 '16 at 15:50
  • 1
    Great to hear that solves your problem, but just to clarify, primary keys DO need to be unique. An index and a primary key share some characteristics, but are not the same thing. You might want to do a little more reading on each, otherwise you're likely to get stumped again on a similar problem. Hope that helps! – Mike W. Dec 19 '16 at 16:39