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 recipient ID is set to NULL by default. In this case, the receiver may NOT also be the owner. I know that this is trivial to do in the logic of progaming, but I wonder if this can be done in the database.

Setting two unique fields: UNIQUE(owner_id, taker_id);does not work, as the owner may have many tickets not accepted at the same time. Maybe a triple unique key along with a ticket identifier, but then I feel that there might be something missing in my design.

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

Answer 1

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"),)

Answer 2

this can be done using restrictions or triggers in mysql.

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

you must ensure that the owner_id <> taker_id clause is always enforced.

this is probably best done on the application side, because you probably want to send a message just before starting the task that assigns the ticket (warnings, status, and much more) ...

mysql, 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 ;

, ( ) . fail - , , .

Answer 3

since mysql does not support the CHECK constraint, I suggest you use a trigger if you want db level