> An index is actually indeed associated with a table, and within that > table with one or more columns. > > Hence, dropping an index doesn't require a table name.
I can easily write SQL like this: CREATE TABLE First (nID, nValue) CREATE INDEX idxID ON First (nID) CREATE TABLE Second (nID, nValue, nConstraint) CREATE INDEX idxID ON Second (nID) SQLite will complain because of the duplicate index names, but in other database packages it will be accepted. You then have to specify the table name when deleting indexes. Don't get me wrong: I'm quite happy with the way that SQLite works. I was concerned that we could accidentally get duplicate index names but that is prevented. Given the variety of ways of specifying the table name in the DROP INDEX statement it seems that this is non-standard SQL anyway. > Because the autoindex is created automatically by sqlite to enforce > the UNIQUE constraint of the PRIMARY KEY. Dropping it would no > longer cause that constraint to be applied, and would then violate > the table definition. This is a fine example of the db system saving > the user's butt. Sure. But does SQLite actually require the UNIQUE constraint in a PRIMARY KEY? I can do this: CREATE TABLE First (nID, nValue) and SQLite will create a rowid field "under the hood" which gives a unique way of identifying each row. It doesn't actually *need* a primary key defined. According to Igor all that happens when you create a primary key is that an index with the unique constraint is created. And the only good reason for not being able to drop it, as far as I can tell, is so that the SQL statement stored for the table is not made invalid. I can create multiple indexes with UNIQUE constraints on the one table. With our data this actually happens in at least one case - we have a unique ID for fragments, and also a field which indicates fragment position via its materialized path, also unique. That is a further indication that PRIMARY KEYS with implicit UNIQUE constraints aren't anything special. Hugh _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
