0

I have a small table in MySQL that is used to control sort order of items on a web page. 

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `type` text NOT NULL,

  `live` text NOT NULL,

  `order` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `id` (`id`)

As above, the order column should not be unique, but in phpMySQLadmin it shows as unique and when even thought I have deleted rows and there are only 5 entries when I search using either of the following, it returns a value like '23' which seems to be an auto-increment value:

SELECT * FROM menus_types ORDER BY `order` DESC LIMIT 1

or 

SELECT MAX( `order` ) FROM menus_types

I have tried using SQL to get index names, using SHOW INDEX FROM menus_types but only the indexes on id column are returned.

If I try ALTER TABLE menus_types DROP INDEX 'unique' or ALTER TABLE menus_types DROP INDEX order as detailed here "sql DROP CONSTRAINT UNIQUE not working" then I get a syntax warning.

Any suggestions as I need to be able to add remove and edit the order column with predictable results?

EDIT AS REQUESTED

    CREATE TABLE `menus_types` 

(  `id` int(11) NOT NULL AUTO_INCREMENT,  

`type` text NOT NULL,  

`live` text NOT NULL,  

`order` int(11) NOT NULL,  

PRIMARY KEY (`id`),  

UNIQUE KEY `id` (`id`)) 

ENGINE=MyISAM 

AUTO_INCREMENT=9 

DEFAULT CHARSET=latin1
Community
  • 1
  • 1
JBReading
  • 27
  • 7

1 Answers1

0

The problem is that 'order' is a reserved word in SQL. If you want to drop it, use 

ALTER TABLE menus_types DROP INDEX `order`

I would recommend you to rename the name of the column, because it can make a lot of trouble, depending on your underlying frameworks.

Chris
  • 8,031
  • 10
  • 41
  • 67
  • Thanks. I have changed the column name to `types_sort_order` as suggested and have run the SQL you posted above, and also tried using the phpMySQLadmin functionality to try and remove the unique constraint but still no joy. In my PHP code I am trying to get the highest value in the `types_sort_order` column, then add one to that to use for the new INSERT `$types_sort_order = mysql_query("SELECT * FROM menus_types ORDER BY `types_sort_order` DESC LIMIT 1");` `$types_sort_order = $types_sort_order + 1;` But this just ends up with a similar problem to what I had initially. – JBReading Aug 09 '13 at 16:40
  • Your answer was correct for the problem I had, but the issue I was seeing with the value of 23 being returned was unrelated to the unique constraint, it was a problem with the SELECT query returning a value of Resource id #22 to which I was adding 1. Thanks anyway. – JBReading Aug 09 '13 at 17:35