CODEWITHSUNDEEP
mysqldatabasemysql-error-1091
Etore Marcari Jr.
Etore Marcari Jr.

Reputation: 582

Can't DROP "MUL" key/index in MySQL - column listed in SHOW

I'm using mysql-server-5.0.45-7.el5 on CentOs 5.

In my database, there is a table which, I don't know when, a MUL key was created (the database is shared, under control of a group), and now when I try to insert some values I get an error like shown above:

Duplicate entry '2-1-2004-09-11 13:13:41.526' for key 2:INSERT INTO ephemeris SET
EPH_TYPE_ID = 1, FILENAME = 'CBERS_2_CCD1_DRD_2004_09_11.13_13_23', ID = 0, 
IS_NEW = 1, SATELLITE_ID = 2, TIME = '2004-09-11 13:13:41.526'

I got this error once, and I tried ALTER TABLE ephemeris DROP INDEX SATELLITE_ID; It worked at first time, but now the same constraint appeared and it didn't worked at all.

The structure of table (resumed):

mysql> show columns from ephemeris;

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| ID           | int(11)      | NO   | PRI | NULL    | auto_increment |
| SATELLITE_ID | int(11)      | NO   | MUL | 0       |                |
| EPH_TYPE_ID  | int(11)      | NO   |     | 0       |                |

When I type the ALTER TABLE command, mysql returns as:

mysql> ALTER TABLE ephemeris DROP INDEX ephemeris.SATELLITE_ID ;
ERROR 1091 (42000): Can't DROP 'SATELLITE_ID'; check that column/key exists

Anybody have already gotten this error? Any help?

Rgds.

Upvotes: 4

Views: 11738

Answers (1)

parampreet bawa
parampreet bawa

Reputation: 71

You need to first drop the foreign key constraint, then drop the index.

mysql> show create table a;

| CREATE TABLE `a` (
`id` varchar(20) DEFAULT NULL,
`sal` int(20) DEFAULT NULL,
`b_id` varchar(20) DEFAULT NULL,
KEY `b_id` (`b_id`),
CONSTRAINT `a_ibfk_1` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

mysql> alter table a drop foreign key `a_ibfk_1`;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table a drop index `b_id`;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

Upvotes: 6

Related Questions