I am treading on uncharted waters (for me), but my understanding is that...
On Tue, 18 Nov 2008 08:07 +0000 (GMT Standard Time), Hugh Gibson <[EMAIL PROTECTED]> wrote: > > > I note that SQLite prevents creation of indexes with the same > > > name, regardless of table. > > > > Quite. So it's unclear why you would want to be able to mention > > table name in the DROP INDEX statement. > > > Fair enough! It does seem strange when an index is associated with a > single table to delete it without specifying the table name. > 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. > > > Other than this one special case, a PRIMARY KEY is implemented > > internally simply by creating an index. Functionally, making a > > column (or a group of columns) PRIMARY KEY or UNIQUE has the same > > effect as creating a UNIQUE index on the same columns. > > > So is there any good reason why I can't delete a primary key by dropping > the index created for it? It has a name like > "sqlite_autoindex_UserPermission_1". Is it because the sql field in > sqlite_master for the table would then be incorrect? 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. > > I get this error when I try to drop it: > > --------------------------- > sqlite3explorer2 > --------------------------- > 1:index associated with UNIQUE or PRIMARY KEY constraint cannot be > dropped. > --------------------------- > OK > --------------------------- > > As there is no functional difference I can stop creating a primary key > and simply create normal indexes. Then if the primary key has to be > changed due to some re-organisation of the table I can drop the index and > create a new one. That avoids having to recreate the whole table. > > > Hugh > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
