Hi, What is your use case (why do you need H2 to not allow two indexes on the same column(s))? I think the implementation complexity to detect this case is relatively large, and I'm not sure if it's really worth it.
All databases I tested (H2, MySQL, HSQLDB, Derby, PostgreSQL, SQLite) didn't fail to create two indexes on the same column. I didn't test Oracle and IBM DB2. My test case is: drop table test; create table test(name varchar(255)); create index idx_n1 on test(name); create index idx_n2 on test(name); If I understand correctly, for you Derby doesn't allow two indexes on the same column? If yes what is the test case? Probably a tool to detect unneeded indexes would be nice. But this would also include indexes that not completely the same, for example: non-unique index on test(name) unique index on test(name, firstName) The first index isn't required usually, as the second index indexes the column "name" as well. However depending on the use case it might make sense to keep both indexes, because index lookup in the first index is slightly faster. Regards, Thomas On Thursday, December 8, 2011, litailang wrote: > Hi. > > I wrote a patch for improving 'create Index' compatibility. > > DB2, Oracle and Derby checks that columns are already indexed when > execute create Index statement. > > Oracle checks that columns already indexed and indexing ascending or > decending. > DB2 checks only that columns already indexed because DB2 has 'allow > reverse scans' option. > > PostgreSQL, MySQL and H2 checks index name already exists only. > > > -- > ----------- > litailang > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
