Duplicated Rows with UNIQUE INDEX MySQL and NULL Columns

Table Sufix: id, name

(3, 'com')

Table DomainName: id, name

(2, 'microsoft')

Table Domain: id, name_code, sufix

(1, 2, 3)    -- microsoft.com

Table SubDomainName:

(4, 'windows')

Table SubDomain: id, name_code, domain

(7, 4, 1)     -- windows.microsoft.com

Table Email: id, name, atserver

(3, 'myemail', 7)    -- myemail@windows.microsoft.com
(4, 'other', 1)      -- other@microsoft.com

Here is a problem of foreign key constraints. How can I resolve domains and subdomains to create emails correctly? I'm having problems with Unique INDEX with NULL values, for example, a solution maybe:

Table Email: id, name, subdomain, domain

(3, 'myemail', 7, NULL)  -- myemail@windows.microsoft.com
(4, 'other', NULL, 1)    -- other@microsoft.com

BUT

(5, 'newemail', NULL, NULL)  -- will duplicated values in the table
(6, 'newemail', NULL, NULL)
(7, 'newemail', NULL, NULL)
(8, 'newemail', NULL, NULL)

AND

(**3**, 'myemail', 7, 1)   -- m开发者_开发问答yemail@windows.microsoft.com and myemail@microsoft.com

How about (5, 'newemail', domain_id/subdomain_id, 'domain/subdomain')

So you could have

(5, 'newemail', 7, 'subdomain') or (5, 'newemail', 1, 'domain')

YOu could still LEFT JOIN SubDomain and Domain table but you will get only the data from the one that you need based on the 'domain/subdomain' field.

That is the quick solution. IMHO your DB structure is not very good and could be optimized. You should keep all domain/subdomain records in one table and use it for the emails. The table should be Table FullDomain: id, name_code, domain_name, subdomain_name

(1, 3, 2, 4) -- windows.microsoft.com

or

(1, 3, 2, 0) -- microsoft.com

Unique in MySQL does only check non-NULL values to be unique. So if you don't like to have more than one of theese lines:

(6, 'newemail', NULL, NULL)

You have to put a unique index over theese last two fields and put values other than NULL (i.e. 0) in them

(6, 'newemail', 0, 0)

Then MySQL will prevent multiple entries.