php - MySQL Unique Key scenario -

i have table consisted of following columns:

table name: user

columns: {id, fk1, fk2, username}

id: primary key

fk1 , fk2 foreign keys coming other tables

there 2 cases:

1) fk1 + username = unique value

2) fk2 + username = unique value

for example:

fk1 = id of country in country table

fk2 = id of state in state table

in countries have states (ex. usa)

what want username of user unique inside state. 2 users different states can have same username, not in same state.

in countries no states (ex. spain)

two users same country cannot have same username. 2 users different countries can have same username.

so, if user registered coming country having states need create unique index columns fk2 , username. [ensure uniqueness per state]

if user registered coming country no states need create unique index columns fk1 , username. [ensure uniqueness per country]

what did far create table users coming states , table users coming country no states. 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 filtering users (users state or not) before inserting data each table. want 1 table users , not two, because when want check if example email registered user (either in states or not) have perform 2 queries (one in each table). or if want extract list of usernames "darklord", regardless of location, again have perform 2 queries.

is there way have this? if yes, how table like? problem starts when user comes country no states, since fk2 column going empty. know unique index allows null value 1 null value.

also 1 general question: how can check if single query on big table faster/slower 2 queries on 2 smaller tables?

ps: storage engine using innodb

thanks in advance.

something 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 select 'value1', 'can', null union select 'value1', 'mex', null union select 'value1', 'usa', 'ak' 

now try inserting of values again , fail because combination exists.

insert #something select 'value1', 'usa', 'ny'