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.


1 Answer 1


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

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

  username (FK to unique_ids.identifier)

  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.

  • Surely this doesn't prevent the same username appearing in both the users and the clients table? The on-insert trigger would have to be used for this. Mar 7, 2013 at 8:45
  • @ChrisSaxon: Good point, the first method still needs a trigger, though a slightly simpler one. Mar 8, 2013 at 15:13
  • 2
    Add a type column to your unique_ids table. Propagate it to both child tables. Include it in FK constraints. Have CHECK constraints verify that type=1 in users table, and type=2 in clients table.
    – A-K
    Mar 8, 2013 at 15:30
  • @AlexKuznetsov - yep, that would work. Mar 8, 2013 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.