On Mon, Nov 18, 2013 at 8:57 PM, RSmith <rsm...@rsweb.co.za> wrote: > Can you honestly use tables without a single unique reference to them?
Plain piles of data abound in quantities so voluminous that there's no time to index them (of course, one would not apply SQLite3 to such a dataset, I'm only pointing out that they exist). In the sciences there are many cases where enormous datasets are produced and consumed (analyzed) on the spot, with no chance of persistent storage of the raw data (much less indexing). There's bound to be datasets where unique keys are not useful or necessary. > Consider your suggestion in carnate here in the following Table on which > some indices may exist but none are Unique: In one case where I've used this column-oriented approach there's no schema for me to enforce: the data is already clean and there will only be multiple attribute value pairs (triples) with the same values where that makes sense (to some consumer, possibly not me) or where collisions are harmless (think of a "comments" attribute). And in this case, because I have no use for such things I can safely accept the UNIQUE constraint and use INSERT OR REPLACE|IGNORE or equivalent constructions such as WHERE NOT EXISTS (...). In another case there's a schema to enforce and I have no use for "sequence multiplicity", only "set multiplicity", therefore a UNIQUE constraint would be fine, but I need to defer enforcement of UNIQUE constraints to the end of each transaction, something that SQLite3 can't do, so I resort to using non-UNIQUE indexes and checking that a transaction adds no collisions as the last step. Even if SQLite3 had deferred UNIQUE constraint enforcement I can think of data where I might like to allow for sequence multiplicity (e.g., as an indirect way of counting things without first having to total them up, though obviously only for things with small cardinality as normally aggregation is to be preferred at all costs; it might be faster to append to a dataset than to update it randomly). > 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 > [...] That seems like the wrong questions to ask. If you chose to allow this dataset then when updating (9, 'monkey') you'd want to update both those rows. You could count them, so you know there's two, and you could delete them and re-add only one (or three) if you like, and this would be true even without any kind of index as long as you could scan the table. > 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. See above. The deferred UNIQUE constraint checking case is a bit of a cop-out: there's still a unique constraint, logically, but implemented at the application layer, with conflicts allowed to exist in uncommitted (but not committed) transactions (but an application transaction might well consist of more than one SQLite3 transaction). The downstream-of-app-that-allows-conflicts case is also a cop-out. More generally however, it's clear that a unique constraint is not necessary to make rowid-less tables work (see the MySQL example), SQL doesn't require unique constraints, and it's not clear that just because you (or I) lack imagination that unique constraints ought to be required. On the contrary, SQLite3 has never required a unique constraint -- the rowid is an implementation detail, one that turns out to not be necessary. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
