Is it possible to create 2 fields that may NOT have the same value ?

Question

Say I have a ticket and this ticket has an owner and a taker. The owner ID is set when the ticket is created and the taker ID is set to default to NULL. In this case the taker may NOT also be the owner. I know this is trivial to do in the progamming logic but I am wondering if it can be done in the database.

Setting the two fields to unique :UNIQUE(owner_id, taker_id); wont work as the owner can have many non-taken tickets at the same time. Perhaps a triple unique key along with the ticket ID, but then I feel there might be something lacking in my design.

An added bonus would be to see how this would be done in Django

Answer 1

this can be done with constraints or triggers in mysql.

http://forge.mysql.com/wiki/Triggers#Emulating_Check_Constraints

you would want to ensure the condition owner_id <> taker_id is always enforced.

this is probably better done on the app side, because you probably want to send an appropriate message immediately before starting up the task that assigns a ticket (alerts, status, and whatnot)...

in mysql, this could be done w/something like:

DELIMITER $$
create trigger self_assign_check before insert on helpdesk.ticket for each row
begin
  if new.taker_id = new.owner_id then
     call fail(’Owner cannot take his own ticket’);
  end if;
end $$
DELIMITER ;

note it will have a (in many scenarios a negligible) effect on inserts. fail is a helper procedure described in the wiki that raises a duplicate key error..

Answer 2

as mysql do not support CHECK constraint i suggest you to use a trigger if you want that on db level

Answer 3

In Django, you can use "unique_together" in your model.

http://docs.djangoproject.com/en/dev/ref/models/options/#unique-together

unique_together = (("owner", "taker"),)