Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
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. –  Chris Saxon Mar 7 '13 at 8:45
    
@ChrisSaxon: Good point, the first method still needs a trigger, though a slightly simpler one. –  FrustratedWithFormsDesigner Mar 8 '13 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. –  AlexKuznetsov Mar 8 '13 at 15:30
    
@AlexKuznetsov - yep, that would work. –  Chris Saxon Mar 8 '13 at 15:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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