Why is this constraint not enforced
Im not sure why this constraint isn't enforced. it's supposed to be enforced for the table payment attribute payment type. The table schema is
payment(orderId, orderNum, paymentType, totalCost)
payment(orderId, orderNum, paymentType, totalCost)
alter table payment
add constraint paymentType
check(value in ('C', 'D', '$'))
Roger Baklund
Mysql does not support check constraints. You can define them without errors, but they are silently ignored, as you have allready experienced.
ASKER
What's my alternative? create a trigger?
If you want it in the db layer, yes. Alternatively you must enforce it in the application layer.
There is another alternative: create a table for it and use a foreign key constraint.
ASKER
how would you make a foreign key constraint to make sure it is one of those three values?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
For shitz how would you create a trigger to enforce this. I tried this but i've never been able to get a trigger to work. it always freezes my phpmyadmin.
delimiter //
create trigger trig before insert on payment
for each row
begin
if new.paymentType = 'C' then insert into payment (paymentType) Values ('C');
elseif new.paymentType = 'D' then insert into payment (paymentType) Values ('D');
elseif new.paymentType = '$' then insert into payment (paymentType) Values ('$');
end if;
end;//
delimiter ;
delimiter //
create trigger trig before insert on payment
for each row
begin
if new.paymentType = 'C' then insert into payment (paymentType) Values ('C');
elseif new.paymentType = 'D' then insert into payment (paymentType) Values ('D');
elseif new.paymentType = '$' then insert into payment (paymentType) Values ('$');
end if;
end;//
delimiter ;
I'm sorry, I don't have much experience with triggers. Ask a new question to let other experts have a go at it.
When running that trigger definition directly in the mysql client, I don't get any errors, but when I try to insert into payment, I get this error:
ERROR 1442 (HY000): Can't update table 'payment' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
There is some discussion about stopping an insert in the comments at the end of this page:
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
When running that trigger definition directly in the mysql client, I don't get any errors, but when I try to insert into payment, I get this error:
ERROR 1442 (HY000): Can't update table 'payment' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
There is some discussion about stopping an insert in the comments at the end of this page:
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html