On 2013/11/19 00:45, Nico Williams wrote:
Thinking about how SQLite3 would implement WITHOUT ROWID, it seems
logical that a set of columns to be used as the primary indexed is
required, but that they be unique shouldn't be.
..and... The implied UNIQUE constraint is just an unfortunate side-effect.
..and...
I can live with this for now, but I can imagine that this will become
a real problem for someone eventually. I can envision SQLite3
requiring an INDEX be declared before any INSERTs on a WITHOUT ROWID
table, .... etc.
Forgetting for a moment that WITHOUT ROWID is a very optional optimisation - Can you honestly use tables without a single unique
reference to them?
Consider your suggestion in carnate here in the following Table on which some
indices may exist but none are Unique:
---| id | attr_name | attr_val |
---|------|-----------------|-------------|
1 | 4 | ape | Gray |
2 | 7 | ape | Silver |
3 | 12 | Lemur | Black |
4 | 9 | monkey | Brown |
5 | 9 | monkey | Brown |
6 | 4 | ape | Gray |
7 | 7 | Lemur | White |
The column to the far left exists so that I can point out rows to you, but it does not exist in the table, only the ones with
headings do. There are no unique indices nor any primary key. How would you construct a query to fix the second monkey reference in
row 5 (say you want it to be another animal) without affecting the other one in row 4? Or just delete the second one and not the
other? Or say make the first monkey white and the second black? Or detect a human error in the work of the researchers who collected
the data?
Unless a Table is just a list of values for which you never want to adjust them, I cannot imagine how you would ever manage a
unique-key-less table. Especially when you make admin software that is supposed to edit tables made by other people and you run
into a gem like the above. What do you tell the user? "Sorry mate, I have no clue how to change the 5th line for you.. either change
both or none... sorry." These rows are still proximitely close and a bit of skullduggery with limits etc. might get you changing
only one of them (albeit indeterminate which one), but can you imagine editing a large table and some row somewhere else changes out
of the view of the user?
It will be a mess.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
