-1

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.

Ved sinha
  • 69
  • 3
  • 9
  • I removed the inconsistent database tags. Please tag only with the database you are really using. – Gordon Linoff Jan 25 '21 at 15:00
  • why not add a integer id column and make that primary key ? – eshirvana Jan 25 '21 at 15:08
  • Hey, read this link [LINK](https://stackoverflow.com/questions/2626158/why-use-multiple-columns-as-primary-keys-composite-primary-key) there is a lot of information about primary key. It is suggested that to use PersonID as you primary key for maintaining database table relationships. – Srijon Chakraborty Jan 25 '21 at 15:11
  • Yeah, it's a viable option, but as I said, it's just a dummy table I made for replicating the error. The real table is being used highly in prod by my company and can't add a random column to it. @eshirvana – Ved sinha Jan 25 '21 at 15:12
  • you are removing a PK column and can't add a unique pk column ! that is weird , anyways , you have no option unless you resolve the duplication – eshirvana Jan 25 '21 at 15:14
  • I just hope I am not the first one in the world to encounter this problem. Removal of a feature involves the removal of columns in the database. Let's see if someone helps. – Ved sinha Jan 25 '21 at 15:17
  • 1
    Your problem is that city was the column which established uniqueness (a primary key is unique key) you either cannot remove city or you have to add something which establishes uniqueness. It's also possible to imagine the same person or entity (imaging chain coffee shops) has a presence in different cities - you may want to consider moving address and city to a junction table to cater for this possibility. – P.Salmon Jan 25 '21 at 15:22
  • Looking at your table I would have guessed that `PersonID` alone would have been a *candidate key*, i.e. a column or group of columns that *could* serve as a primary key, but it seems I would have guessed wrong. It makes me wonder what its semantics are. – Booboo Jan 25 '21 at 16:52
  • Hey, @Booboo Please read the last lines, that's just a dummy table. – Ved sinha Jan 25 '21 at 18:23
  • @P.Salmon can you please elaborate a bit – Ved sinha Jan 25 '21 at 18:24
  • Elaborate? On what the duplicate key issue or the junction table suggestion – P.Salmon Jan 25 '21 at 18:45
  • @P.Salmon moving 2 attributes to separate junction table, this one, how does it help in removal of the column? – Ved sinha Jan 25 '21 at 21:17
  • You know what the problem is, if you want to maintain table schema as is then you have to remove duplicates based on id.first_name,last_name (before adding the primary key back in)- there are lots of answers on this site about how to do that, - I have no idea how you would establish which of the duplicates to keep , only you can decide. – P.Salmon Jan 27 '21 at 07:50
  • As for the junction table suggestion my view is that address is not an attribute of person. For example I have an account with an online retailer where I have a billing address which is also a delivery address but when I buy stuff for relatives I want it delivered to their address(s).The addresses could be stored in a junction table with my id ,address type, address including city and zip code. – P.Salmon Jan 27 '21 at 07:54

1 Answers1

0

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.

Edit: If you get any better solution, kindly let me know.

Abbasi
  • 31
  • 6