Sql two multique unique ignore when first case ocours

My table has four columns city, zipcode, number and extra. I created unique group for city, zipcode and number called unique1 and another group for city,zipcode,number and extra called unique2. Those groups need to be unique but the problem is that I can have non unique values when extra if different or is null. For example:

 city | zipcode | number | extra
  A      123       123      null
  A      123       123      10  (I cant add this row because of the unique groups)

How can I solve this problem? (I`m using Mysql)

In another words, what I need is a way to:

1) The grouping of city, zipcode and number must be unique if extra is null 2) If extra isn’t null I’d like to insert that information even if the new row collides with the unique rule on ‘1’.

Answer

In MySQL, using unique indexes to handle data constraints beyond simple ones is not a great idea. Other, more expensive, table servers have more elaborate ways to describe constraints.

Your first unique index (you called it a “group”) — unique1 — prevents the second row in your example from being INSERTed to your table.

Edit: Your example shows that you require non-unique values for your first three columns.

I’m guessing a bit, but I think you should drop unique1 and just use unique2.