Search code examples
performancesqliteindexingprimary-keyauto-increment

Multicolumn index .. one is Autoincrement

I created the table patient in my database :

create table patient(n° integer primary key autoincrement, firstname varchar(30),lastname varchar(30)...ect);

But in my application ,there is a textfield where researching for patient information by n° or name or surname of patient, I think it is more preferable to index the table by n°+firstname+surname to query data faster.

How can i solve it and verify that the n°patient is always autoincrementing although it is not primary key thank you!

Solution

  • In this case have basically designed your primary key to be the surrogate PatientNo. There is no avoiding that.

    The solution is to create a non-unique index on (lastname,firstname) to assist in name lookups.