Re: [sqlite] Dropping and creating indexes
On Nov 20, 2008, at 4:39 PM, Hugh Gibson wrote: >>> 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. > > Ah, OK. So there is an internal implication of PRIMARY KEY vs a UNIQUE > index. Thanks. No. There is no difference in the implementation of a PRIMARY KEY and a UNIQUE index (unless the PRIMARY KEY happens to be an INTEGER PRIMARY KEY, in which case it is an alias for the table's rowid). The first part of the statement you are responding to is a bit misleading. You were correct when you said that an automatically created index cannot be dropped because dropping it would prevent SQLite from correctly implementing the PRIMARY KEY or UNIQUE clauses of the CREATE TABLE declaration. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dropping and creating indexes
> > 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. Ah, OK. So there is an internal implication of PRIMARY KEY vs a UNIQUE index. Thanks. Hugh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dropping and creating indexes
On Wed, 19 Nov 2008 06:59 + (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 ON (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
Re: [sqlite] Dropping and creating indexes
> > 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. > > The only database I'm aware of that does this is SQL Server. > Don't overgeneralize. :) No worries - I was just going from the list of syntaxes for this from the original posting - see http://www.w3schools.com/SQL/sql_drop.asp Shall we say "but in *some* other database packages" ? ;-) Hugh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dropping and creating indexes
> 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. The only database I'm aware of that does this is SQL Server. Don't overgeneralize. :) -T ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dropping and creating indexes
> 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
Re: [sqlite] Dropping and creating indexes
I am treading on uncharted waters (for me), but my understanding is that... On Tue, 18 Nov 2008 08:07 + (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
Re: [sqlite] Dropping and creating indexes
> > 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. > 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? 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
Re: [sqlite] Dropping and creating indexes
"Hugh Gibson" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I see from the syntax of DROP INDEX that a table name is not > specified. Are there any plans for adding a way of specifying the > table? > > 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. > Also, is there any good reason for creating a primary key versus > creating a separate index afterwards? INTEGER PRIMARY KEY column has a special meaning in SQLite: http://sqlite.org/autoinc.html 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. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
