> > You could achieve the same result as follows: > > > > create table x (x, y, z); > > create unique index pk_x_x on x(x); > > > > except that now you have given the index on x an explicit name and one > does not have to be manufactured for you. > > > > I checked these indexes gets created even if I manually define similar > indexes. But I don't mind. After some experiments, this seems more related > to ROWID, and you seem to confirm a relation, above.
Well, yes, of course. If you ask for two indexes to be created, two indexes will be created. create table x (x primary key, y, z); create unique index pk_x_x on x(x); requests the creation of two indexes. One by the "primary key" contained in the table definition, and one by the create index. If you only want one, then only say to create one. https://www.sqlite.org/optoverview.html#autoindex see especially the last paragraphs of that section, and also https://www.sqlite.org/fileformat2.html#intschema Except for without_rowid tables, specifying "primary key" and "unique" within the table definition is just syntactic sugar for creating the indexes manually using create index; much in the same way ... JOIN ... ON ... is merely syntactic sugar for FROM ..., ... WHERE ... (except in the case of outer joins and the non-standard CROSS JOIN which cannot be expressed without the sugar).
