[sqlite] Unique index versus unique constraint
Paul van Helden
When I create a table and specify a unique constraint, a unique index is
automatically created. This index cannot be dropped, so the only way to get
rid of the uniqueness is to recreate the table without the constraint. No
problem.
When I create a table without the unique constraint, I can add the unique
requirement later by creating a unique index (if it works!). Apart from
behaving the same for INSERTS, this is not exactly the same because I can
simply drop the index to remove the unique requirement.
So my problem is, I need to know how the unique requirement was created in
the first place in order to get rid of it in the appropriate manner.
One solution would be to parse the SQL field in sqlite_master to look for
the constraint. (I'd prefer not!). I also don't really want to attempt a
DROP INDEX and then fall back to recreating the table since I am generating
scripts.
Another solution that ocurred to me is to check for "sqlite_autoindex_" in
the name field of pragma index_list(tablename). Can I simply assume that
unique indexes named sqlite_autoindex_* cannot be dropped?
Perhaps if pragma table_info(tablename) had a "unique" column like it has a
"notnull" column, but only for unique constraints on single fields. Or if
pragma index_list had a "constraint" (or "cantdrop" or something) column
that would indicate that the index cannot be dropped.
Am I missing something? How do you solve this problem?
Regards,
Paul.
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Simon Slavin
On 18 Mar 2011, at 9:58am, Paul van Helden wrote:
> So my problem is, I need to know how the unique requirement was created in
> the first place in order to get rid of it in the appropriate manner.
>
> One solution would be to parse the SQL field in sqlite_master to look for
> the constraint. (I'd prefer not!). I also don't really want to attempt a
> DROP INDEX and then fall back to recreating the table since I am generating
> scripts.
Surely faster to look for an appropriate INDEX declaration in sqlite_master marked as UNIQUE.
Simon.
BareFeetWare
> Another solution that ocurred to me is to check for "sqlite_autoindex_" in
> the name field of pragma index_list(tablename). Can I simply assume that
> unique indexes named sqlite_autoindex_* cannot be dropped?
Automatic indexes have no "SQL" entry in the SQLite_Master table, so you can use that to see if you can drop it. eg:
select Name from SQLite_Master where type = 'index' and SQL is null
will give you the name of all the automatic indexes, which can't be dropped.
> Perhaps if pragma table_info(tablename) had a "unique" column like it has a
> "notnull" column,
Yes, we definitely need more extensive schema introspection facilities in SQLite.
Thanks,
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
Paul van Helden
> Automatic indexes have no "SQL" entry in the SQLite_Master table, so you
> can use that to see if you can drop it. eg:
>
> select Name from SQLite_Master where type = 'index' and SQL is null
>
> will give you the name of all the automatic indexes, which can't be
> dropped.
>
Thanks Tom,
It actually makes sense to never drop any automatic index (!). My question
should have been how to reliably determine whether an index is automatic or
not. Unless someone can come up with a compelling reason not to test for
"sqlite_autoindex_" in pragma index_list, I'd prefer to stick to that.
Now I'm wondering if in future a situation could arise where the SQL will be
provided for automatic indexes... Eg. to aid in exporting DDL.
>
> Yes, we definitely need more extensive schema introspection facilities in
> SQLite.
>
A simple "autoindex" field in pragma index_list would be a good start :-)
Simon Slavin
On 19 Mar 2011, at 7:52am, Paul van Helden wrote:
> It actually makes sense to never drop any automatic index (!). My question
> should have been how to reliably determine whether an index is automatic or
> not. Unless someone can come up with a compelling reason not to test for
> "sqlite_autoindex_" in pragma index_list, I'd prefer to stick to that.
>
> Now I'm wondering if in future a situation could arise where the SQL will be
> provided for automatic indexes... Eg. to aid in exporting DDL
I'm not sure why you'd want it. You can't do anything with automatic indexes anyway: they can't be dropped. The only indexes you can DROP are the ones which have their own listing in the sqlite_master table.
If you're trying to figure out the TABLE's contraints, you can't use indexes anyway. You have to do it by looking at the column definition and the foreign keys, and these have their own entries in sqlite_master.
Simon.