Setting field to unique per user and not column

[learningprobs]

Hello,

 

I would like a little bit of guidance please to make sure I am not going to code something bad and have to redo it down the line.

 

I have these fields:

TAble = delivery_addresses

 

FIELDS = 

    user_id,

    company,

    name,

    surname,

    street,

    town_id,

    postcode,

    countries_id,

    primary

 

 

Each user has multiple delivery addresses but only 1 can be set as a primary = 1 at any one time.

My problem is how to set only 1 field to 1 but have all the others back to 0 per user.

 

With a bit of research I have this command(user = id of 170)

 

UPDATE delivery_addresses SET primary=IF(id=170, 1, 0)

 

From what I understood is that "if the user with the id of 170 is found, set the primary column to 1 and all the others to 0".

 

Am I correct to assume this?

 

 

 

Now if I wish to INSERT an address and set it as primary from the start, this will then require 2 queries, one insert that sets the primary to 0 and 1 to update it back to 1, is this the right way of doing it?

 

 

Thank you in advance.

 

 

 

 

  

Edited October 28, 2016 by learningprobs

[learningprobs]

Ok, it seems that this works fine:

 

   UPDATE delivery_addresses SET
      primary_address = IF(address_id=5, 1, 0)
      WHERE user_id = 179

 

Could anyone confirm please that this is the right way to set 1 field to 1 while changing all the others to zero? Will this not cause a big load on the server when more and more rows are added?

 

Thanks,

Edited October 28, 2016 by learningprobs

[Barand]

You would lessen the load by putting an index on the user_id column.