sql - Removing a Key from MySQL Table

Question

I am trying to remove the city from this MySQL table -

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PersonID  | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   | PRI | NULL    |       |
| FirstName | varchar(255) | NO   | PRI | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

Now city along with 3 other attributes happens to be a primary key and when I execute -

mysql> ALTER TABLE Persons DROP PRIMARY KEY;

It will remove all the Primary Keys.

mysql> DESC Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PersonID  | int(11)      | NO   |     | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | NO   |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

But now when I am adding all the keys back without the city, it's giving an error -

mysql> ALTER TABLE Persons ADD PRIMARY KEY(PersonID, LastName, FirstName);
ERROR 1062 (23000): Duplicate entry '100-SINHA-VED' for key 'PRIMARY'

Now due to duplicate values in the table, in which only city was different, I can't make the other 3 as primary keys and also I can't delete the data as the problem is on some other table, can't expose the table, so made this sample table "Persons" and replicated the problem.

How to tackle this problem, have never encountered it before.

question from:https://stackoverflow.com/questions/65887219/removing-a-key-from-mysql-table

Answer 1

As far as, I understand your problem. I think composite keys might be the solution for this problem. I guess, click here for more info.