1

Is there a way by which update to ALL rows of a Mysql table could be denied or avoided?

Background:

I have a table 'Orders' having 'OrderID' (Type VARCHAR) and 'VendorID' (Type Integer) as two of the columns. If I run the below query on this table, it simply resets VendorID in all the rows:

SET `VendorID` = 0 WHERE `OrderID` = 0

OrderID should not be 0 but because of some bug in the system, it escaped all the checks and created havoc. Had OrderID been of Integer type, this query would not have done anything. But since OrderID has alphabets, it could not be set as Integer type.

Can Mysql be configured in such a way that it rejects Updates to all rows? Would result of this query change if underlying database is not Mysql?

My environment:

nginx/1.4.6 Codeigniter (2.x) / Mysql (5.5) / InnoDB table type

Please suggest how this issue could be avoided in future, thanks.

2
  • 1
    I voted to migrate this to dba.stackexchange.com as I think that's a far more appropriate site for your question.
    – user143703
    Apr 13, 2017 at 19:47
  • Thanks, don't know how to migrate so re-posted there [dba.stackexchange.com/questions/170980/… as well
    – AAgg
    Apr 14, 2017 at 3:37

1 Answer 1

1

Your question on DBA Stack Exchange was marked as a duplicate of a casting question... I doubt casting your data would have solved the issue so I'm using this question to answer.

Order_id sounds like it should be a primary key (both unique and not null)...

alter table orders
add primary key (order_id);

If order_id is not a good primary key (i.e., it is nullable), then make it a unique key instead...

alter table orders
add constraint orders_orderid_uk
    unique (order_id);
1
  • How does changing order id to primary key prevent resetting of vendor id, I could not understand? Issue is vendor id getting reset to 0 for all orders, not the other way round. And yes, Order ID is indeed unique but could not be made as Primary Key since it contains characters as well (type VARCHAR).
    – AAgg
    Apr 15, 2017 at 3:37

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