CODEWITHSUNDEEP
databasesqlite
kappa
kappa

Reputation: 639

Sqlite. How to create an index in attached DB?

I have a problem with adding index to memory database attached to main database.

1) I open the database (F) from file

2) Attach the :memory: (M) database

3) Create tables in database M

4) Copy data from F to M

I would also like to create an index in database M, but don't know how to do that. This code creates index but in F database:

sQuery = "CREATE INDEX IF NOT EXISTS [INDID] ON [PANEL]([ID]  ASC);";

I tried to add the name qualifier before table name like this:

sQuery = "CREATE INDEX IF NOT EXISTS [INDID] ON [M.PANEL]([ID]  ASC);";

but SQLite returns with message that column main.M.PANEL does not exist.

What can I do?

Upvotes: 12

Views: 5825

Answers (2)

pushkar gupta
pushkar gupta

Reputation: 1

Try this :

CREATE UNIQUE INDEX `idxuserID` ON `userFlag` (
    `userID`
);

Upvotes: -1

newtover
newtover

Reputation: 32094

just put the square brackets around the prefix [M].[panel] or just skip them

UPD: you shoud set prefix before the index name instead of the table name:

sqlite> attach database ":memory:" as m;
sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------
0    main
2    m
sqlite> create table m.users (id int, name TEXT);
sqlite> create unique index m.qwe on users (name);
sqlite> insert into m.users VALUES(2,'asd');
sqlite> insert into m.users VALUES(3,'asd');
Error: column name is not unique

Upvotes: 19

Related Questions