CODEWITHSUNDEEP
mysql
BigBug
BigBug

Reputation: 6290

Key 'emp_no' doesn't exist in table 'employees'

why does something like this not work?

mysql> SELECT * FROM employees FORCE INDEX(emp_no) WHERE emp_no = '77899';

where the table looks like this:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

the error i get is this:

ERROR 1176 (42000): Key 'emp_no' doesn't exist in table 'employees'

Upvotes: 1

Views: 4658

Answers (3)

Jared Dominic Caraan
Jared Dominic Caraan

Reputation: 19

You can add it to the index list first via this query.

ALTER TABLE employees ADD INDEX emp_no (emp_no);

Upvotes: 1

Alkanshel
Alkanshel

Reputation: 4429

I think it's complaining (very unhelpfully) because you're doing force index on something that's already an index. Do SHOW INDEXES FROM <<tablename>> to be sure. You shouldn't need the force index clause..

Upvotes: 1

Rizwan Kassim
Rizwan Kassim

Reputation: 8121

Just because emp_no is the name of the primary key doesn't mean that it's the name of the index.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is PRIMARY. To see the index names for a table, use SHOW INDEX. http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

do this instead

mysql> SELECT * FROM employees FORCE INDEX(PRIMARY) WHERE emp_no = '77899';

Upvotes: 3

Related Questions