MySQL update unique value returns Error Code: 1062. Duplicate entry-CodePudding

I have a problem with updating the unique column value in the table.

I have a customer table and I have a separate customer_address table where the customer can have one primary address and others. The primary address is the main address to which the goods are delivered. Customers can have many addresses but one must be primary.

When I try to update I get the message: Error Code: 1062. Duplicate entry '0-3' for key 'primary_UNIQUE'

CREATE TABLE `customer_address` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) DEFAULT NULL,
      `address` varchar(525) NOT NULL,
      `customer_id` int(11) NOT NULL,
      `primary` tinyint(4) DEFAULT '0',
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `primary_UNIQUE` (`primary`,`customer_id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

My plan was that all non-primary addresses have a value of 0, and only the primary address has a value of 1.

id   |  name        |      address             |     customer_id   |   primary
------------------------------------------------------------------------------       
'2',  'Store 1',     'Address name 24',           '3',                '0'
'3',  'Wholesale',   'Address name 24',           '3',                '1'
'4',  'Store 2',     'Address name 24',           '1',                '0'
'5',  'Wholesale 3', 'Address name 24',           '1',                '1'

How I try to change primary address:

First, I need to update id = 3 and set primary to 0. This will cause Duplicate entry

UPDATE customer_address SET primary = 1 WHERE id=2;
UPDATE customer_address SET primary = 0 WHERE id=3;

Is there another way to solve this?

CodePudding user response：

First of all I think your table structure is contradicting the design you posted,

I have a customer table and I have a separate customer_address table where the customer can have one primary address and others. The primary address is the main address to which the goods are delivered. Customers can have many addresses but one must be

With the above table design you can have only two entries per customer_id so you will never be able to store more then 2 addresses per customer, and one will be primary and the other being the non-primary.

For this you might need to redesign your table first to fulfill the requirements you have.

CodePudding user response：

So, a customer's primary address is to become the secondary address and secondary address is to become primary address. The problem is that if you try to modify either of them, then the operation will fail because of the other.

However, we know that NULL = NULL is a false criteria, so, you need to set one of them to NULL, update the other and then update the nulled one:

UPDATE customer_address SET primary = NULL WHERE id=3;
UPDATE customer_address SET primary = 1 WHERE id=2;
UPDATE customer_address SET primary = 0 WHERE id=3;