SQL Uniqueness Across Multiple Tables

Question

I have two SQL tables: Users and clients.

Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field.

The users table has a username field which is unique.

The clients table has a user_id field AND clientname field that together are unique (i.e. clientnames can exist more than once, but must have different user_ids)

My question is: How do I add a rule that forces uniqueness between the username field in the users table and the clientname field in the clients table? Is it even possible?

i.e. no username can be the same as a clientname , and no clientname can be the same as a username.

Answer 1

You could have a third table which stores both. Something like this:

unique_ids
----------
  identifier (UNIQUE)
  used_where (name/id of table that the identifier is used in)

users
-----
  username (FK to unique_ids.identifier)

clients
-------
  clientname (FK to unique_ids.identifier)

This way, both tables share one common table that contains the values that need to be unique and uniquness contraints can be easily enforced in that shared table. You will need a simple on-insert trigger (on both tables) as well that will check the used_where field to ensure that the identifier is not already used by the other table (because if I understand your requirement, username must be unique in users but clientname does not need to be unique in clients).

If you don't want to introduce new structures and modify existing ones, you could try using an on-insert trigger on clients and users that checks to make sure a new record does not contain an existing username/clientname.