> One occasionally sees SQLite schemas of the following form: > > CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ....); > > In other words, one sometimes finds a PRIMARY KEY and a UNIQUE > declaration on the same column. This works fine in SQLite, but it is > wasteful, both of disk space and of CPU time.
I'm trying to reproduce the issue, but I only see a single index. What am I missing? SQLite version 3.5.3 Enter ".help" for instructions sqlite> CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar); sqlite> select * from sqlite_master; table|meta|meta|2|CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar) index|sqlite_autoindex_meta_1|meta|3| The database file above has 3 pages, as one would expect. SQLite version 3.6.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t1(id LONGVARCHAR UNIQUE, foo, bar); sqlite> create table t2(id LONGVARCHAR PRIMARY KEY, foo, bar); sqlite> create table t3(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar); sqlite> select * from sqlite_master; table|t1|t1|2|CREATE TABLE t1(id LONGVARCHAR UNIQUE, foo, bar) index|sqlite_autoindex_t1_1|t1|3| table|t2|t2|4|CREATE TABLE t2(id LONGVARCHAR PRIMARY KEY, foo, bar) index|sqlite_autoindex_t2_1|t2|5| table|t3|t3|6|CREATE TABLE t3(id LONGVARCHAR UNIQUE PRIMARY KEY, foo, bar) index|sqlite_autoindex_t3_1|t3|7| And this database has 7 pages. -- View this message in context: http://www.nabble.com/On-UNIQUE-and-PRIMARY-KEY-tp19313570p19325957.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
