MySQL唯一密钥方案

Question

I have a table that is consisted of the following columns:

Table name: User

Columns: {id, fk1, fk2, username}

id: is the primary key

fk1 and fk2 are foreign keys coming from other tables

There are 2 cases:

1) fk1 + username = unique value

2) fk2 + username = unique value

For example:

fk1 = the id of a country in the country table

fk2 = the id of a state in the state table

In countries that have states (ex. USA)

What i want is the username of the user to be only unique inside a state. Two users from different states can have the same username, but not in the same state.

In countries with no states (ex. Spain)

Two users from the same country cannot have the same username. Two users from different countries can have the same username.

So, if the user registered is coming from a country having states i need to create a unique index for columns fk2 and username. [ensure uniqueness per state]

If the user registered is coming from a country with no states i need to create a unique index for columns fk1 and username. [ensure uniqueness per country]

What i did so far was to create a table for users coming from states and a table for users coming from a country with no states. Like below:

--Table name: User_States

Columns: {id, fk2, username}

Unique index on fk2 + username

--Table name: User

Columns: {id, fk1, username}

Unique index on fk1 + username

This solution can work by filtering the users (users from state or not) before inserting the data into each table. But i want only one table with all users and not two, because when i want to check if for example an email is already registered by a user (either in states or not) i have to perform 2 queries (one in each table). Or if i want to extract a list of all usernames like "darklord", regardless of their location, again i have to perform 2 queries.

Is there a way to have this? If yes, how will the table look like? My problem starts when the user comes from a country with no states, since the fk2 column is going to be empty. I know that a unique index allows Null value but only one Null value.

Also one general question: How can i check if a single query on a big table is faster/slower than 2 queries on 2 smaller tables?

PS: The storage engine i am using is InnoDB

Thanks in advance.

Answer 1

Something like this work?

create table #Something
(
    SomeDataField varchar(10) not null,
    CountryCode char(3) not null,
    StateCode char(2) null,
    CONSTRAINT UniqueCountryState UNIQUE NONCLUSTERED
    (
        SomeDataField, CountryCode, StateCode
    )
)

insert #Something
select 'Value1', 'USA', 'NY' union all
select 'Value1', 'CAN', null union all
select 'Value1', 'MEX', null union all
select 'Value1', 'USA', 'AK'

Now try inserting any of those values again and it will fail because the combination already exists.

insert #Something
select 'Value1', 'USA', 'NY'