I created a table and assigned a UNIQUE CONSTRAINT to a specific column.
我创建了一个表并为特定列分配了一个UNIQUE CONSTRAINT。
... column_name INT UNSIGNED NOT NULL UNQUE
Now I no longer want the column to have the unique constraint, and so I tried modifying that:
现在我不再希望列具有唯一约束，因此我尝试修改它：
ALTER TABLE mytable DROP INDEX column_name
like I saw here.
就像我在这里看到的
The above query executes successfully the first time. But when I try inserting duplicate values in the column_name column, I still get the error
上述查询第一次成功执行。但是当我尝试在column_name列中插入重复值时，我仍然会收到错误
#1062 Duplicate entry '10' for key column_name_2
Which, I presume, means the Constraint still remains on the table. (Also funny how _2 gets appended to the column name). But if I repeat the above ALTER statement, I get
我认为，这意味着约束仍然存在于桌面上。 （同样有趣的是如何将_2附加到列名称）。但是，如果我重复上述ALTER声明，我会得到
#1091 - Can't DROP 'column_name'; check that column/key exists
I also tried
我也试过了
ALTER TABLE mytable DROP INDEX UNIQUE
and it gives me the error
它给了我错误
#1064 - 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 'UNIQUE' at line 1
Don't know if I'm missing something, but how do I remove the UNIQUE constraint from this column?
不知道我是否遗漏了什么，但是如何从该列中删除UNIQUE约束？
1 个解决方案
#1
0
The issue came up because I had earlier ran the following query, in trying to remove the UNIQUE constraint.:
问题出现了，因为我之前运行了以下查询，试图删除UNIQUE约束：
ALTER TABLE mytable CHANGE column_name column_name INT UNSIGNED NOT NULL
This somehow created a new column called column_name_2 and assigned it the UNIQUE constraint. So when I ran SHOW CREATE TABLE like @Paolof76 suggested, I found among the columns:
这以某种方式创建了一个名为column_name_2的新列，并为其指定了UNIQUE约束。所以，当我像@ Paolof76建议的那样运行SHOW CREATE TABLE时，我在列中找到了：
UNIQUE KEY `column_name_2` (`column_name`)
which i presume got created due to the ALTER statement above. So I ran
我认为是由于上面的ALTER语句而创建的。所以我跑了
ALTER TABLE mytable DROP INDEX column_name_2
which eliminated the unique key and solved my problem. Thanks for the heads-up @Paolof76!
这消除了独特的关键并解决了我的问题。感谢单挑@ Paolof76！
#1
0
The issue came up because I had earlier ran the following query, in trying to remove the UNIQUE constraint.:
问题出现了，因为我之前运行了以下查询，试图删除UNIQUE约束：
ALTER TABLE mytable CHANGE column_name column_name INT UNSIGNED NOT NULL
This somehow created a new column called column_name_2 and assigned it the UNIQUE constraint. So when I ran SHOW CREATE TABLE like @Paolof76 suggested, I found among the columns:
这以某种方式创建了一个名为column_name_2的新列，并为其指定了UNIQUE约束。所以，当我像@ Paolof76建议的那样运行SHOW CREATE TABLE时，我在列中找到了：
UNIQUE KEY `column_name_2` (`column_name`)
which i presume got created due to the ALTER statement above. So I ran
我认为是由于上面的ALTER语句而创建的。所以我跑了
ALTER TABLE mytable DROP INDEX column_name_2
which eliminated the unique key and solved my problem. Thanks for the heads-up @Paolof76!
这消除了独特的关键并解决了我的问题。感谢单挑@ Paolof76！