Should i have a primary ID? i am indexing another field

Using sqlite i need a t开发者_如何学JAVAable to hold a blob to store a md5 hash and a 4byte int. I plan to index the int but this value will not be unique.

Do i need a primary key for this table? and is there an issue with indexing a non unique value? (I assume there is not issue or reason for any).

Personally, I like to have a unique primary id on all tables. It makes finding unique records for updating/deleting easier.

How are you going to reference on a SELECT * FROM Table WHERE or an UPDATE ... WHERE? Are you sure you want each one?

You already have one.

SQLite automatically creates an integer ROWID column for every row of every table. This can function as a primary key if you don't declare your own.

In general it's a good idea to declare your own primary key column. In the particular instance you mentioned, ROWID will probably be fine for you.

My advice is to go with primary key if you want to have referential integrity. However there is no issue with indexing a non unique value. The only thing is that your performance will downgrade a little.

What are the consequences of letting two identical rows somehow get into this table?

One consequence is, of course, wasted space. But I'm talking about something more fundamental, here. There are times when duplicate rows in data give you wrong results. For example, if you grouped by the int column (field), and listed the count of rows in each group, a duplicate row (record) might throw you off, depending on what you are really looking for.

Relational databases work better if they are based on relations. Relations are always in first normal form. The primary reason for declaring a primary key is to prevent the table from getting out of first normal form, and thus not representing a relation.