On Wed, 19 Nov 2008 06:59 +0000 (GMT Standard Time), Hugh Gibson <[EMAIL PROTECTED]> wrote: > > 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.
Are you worried about what SQLite does or are you worried about what all possible databases in the world can or may do? For other dbs, you will have to ask on their respective email lists. In SQLite, afaik, you have to specify the <index name> ON <table name> (column name(s)); and yes, each index has be uniquely named, hence each unique name is associated with a unique table, hence when dropping an index, you have to specify the index name only. > > 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. > Don't know the standard SQL way, but creators of SQLite make a huge deal about SQLite being extremely SQL standard compliant, and I tend to believe them. > > > 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. Yes, however, if you do define a PRIMARY KEY then that internal index is used instead of the ROWID, hence, you can't delete that because it would violate what you told the db to do otherwise. > > 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 > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
