indexs but not unique?

Databases
1

I’m trying to create a table that has two indexes.

sql = _ "CREATE TABLE IF NOT EXISTS `" + DatabaseName + "`.`" + Tablename + "` (" +_ "`JobID` INT NOT NULL, " +_ "`cmd` VARCHAR(512) NOT NULL, " +_ "`State` INT NOT NULL, " +_ "`message` VARCHAR(256) NULL, " +_ "UNIQUE INDEX `JobID_UNIQUE` (`JobID` ASC), " +_ "UNIQUE INDEX `State_UNIQUE` (`State` ASC)) " +_ "ENGINE = InnoDB;"

This came out of MySQLWorkbench.
The problem I believe is that JobID and State are not unique, but I’d still like to have an index into these rows.
(like an ISAM file… you find the first entry and then sequentially go through all results)

What must I do to my my Create Table sql to have indexes but not necessarily unique?

2

remove the word UNIQUE

3

Hi Dave, I sent you a private message can you check it. Thanks.

4

Should I have created the index if it didn’t exist, as a separate sql statement perhaps?

5

It’s ok to create the indexes separately, but it won’t resolve the issue you were having. You’d have to remove the UNIQUE constraint either way.

6

I did that… A bit off topic but I wonder why MySQLWorkbench wouldn’t allow me to create ‘non-unique’ indicies…

7

WorkBench is a joke. Look around at the free and paid alternatives.