How can I drop the "Unique Key Constraint" on a column of a MYSQL table using "Phpmyadmin".
Thanks in advance
+2
A:
A unique constraint is also an index.
First use SHOW INDEX FROM tbl_name to find out the name of the index. The name of the index is stored in the column called key_name in the results of that query.
Then you can use DROP INDEX:
DROP INDEX index_name ON tbl_name
or the ALTER TABLE syntax:
ALTER TABLE tbl_name DROP INDEX index_name
Mark Byers
2010-08-15 14:15:30
Sorry sir i had tried it before raising the question ,but its not working
Ankur Mukherjee
2010-08-15 14:26:08
@Ankur Mukherjee: I forgot to mention: You have to change tbl_name and index_name to the name of your actual table and the name of the actual index, respectively. You can see the names in MySQL Query Browser. You can also do `SHOW CREATE TABLE tbl_name`.
Mark Byers
2010-08-15 14:33:30
Sir I know that very well and i had tried that only
Ankur Mukherjee
2010-08-15 14:34:46
@Ankur Mukherjee: What is the error message that you get?
Mark Byers
2010-08-15 14:51:22
Error Message:"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(player_id,year,tournament)' at line 1"
Ankur Mukherjee
2010-08-15 14:56:44
@Ankur Mukherjee: That's because you're not using the name of the index. The name of the index is defined when you create the index. Find out the name of the index and use the index name in the DROP INDEX statement.
Mark Byers
2010-08-15 14:59:05
You can also use `SHOW INDEX FROM tbl_name` to show information about all indexes on a table, including the name of the index.
Mark Byers
2010-08-15 15:00:21
INDEX name refers to the name of the column on which "UNIQUE KEY" constraint is specified isn't it sir?
Ankur Mukherjee
2010-08-15 15:01:43
@Ankur Mukherjee: No, that is not correct. The index name might or might not be the same as the column name. Usually it is not.
Mark Byers
2010-08-15 15:03:00
so there is no way of just removing the unique constraint of a column?(other than removing index)
Ankur Mukherjee
2010-08-15 15:04:36
@Ankur Mukherjee: The way I have suggested is in my opinion the best way to do it.
Mark Byers
2010-08-15 15:33:26
A:
The indexes capable of placing a unique key constraint on a table are PRIMARY and UNIQUE indexes.
To remove the unique key constraint on a column but keep the index, you could remove and recreate the index with type INDEX.
Note that it is a good idea for all tables to have an index marked PRIMARY.
thomasrutter
2010-08-15 14:28:03
A:
You can DROP a unique constraint from a table using phpMyAdmin as requested as shown in the table below. A unique constraint has been placed on the Wingspan field. The name of the constraint is the same as the field name, in this instance.
Geoffrey Van Wyk
2010-10-06 17:42:04