Update field troubles

[Scooby08]

I have an invoice type of system and am having troubles trying to update an item.. I have 3 tables that deal with the orders.. Here they are:

CREATE TABLE `dw_orders` (
  `order_id` INTEGER unsigned NOT NULL auto_increment,
  `customer_id` INTEGER unsigned NOT NULL,
  `order_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`order_id`)
)

CREATE TABLE `dw_order_items` (
  `order_item_id` INTEGER unsigned NOT NULL auto_increment,
  `order_id` INTEGER unsigned NOT NULL,
  `item_id` INTEGER unsigned NOT NULL,
  `item_qty` INTEGER unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY  (`order_item_id`)
)

CREATE TABLE `dw_items` (
  `item_id` INTEGER unsigned NOT NULL auto_increment,
  `item_category` varchar(255) NOT NULL default '',
  `item_description` text NOT NULL,
  `item_price` float default NULL,
  PRIMARY KEY  (`item_id`)
)

The field I'm trying to update is item_id in the dw_order_items table.. I am using this query to update:

$query  = "UPDATE dw_order_items ";
$query .= "SET item_id = '$_POST[new_item_id]' ";
$query .= "WHERE item_id = $_POST[old_item_id_wp] ";
$query .= "AND order_id = $_POST[order_id] ";

It works fine, but problems arise when more orders are added.. There are then multiple fields that can have the same order_id and the same item_id.. So when I run that query above it changes all item_id's with the same old_item_id and the same order_id..

How can I make these items more unique so the update will not change all orders and items at once, and rather just the targeted item_id??

[mcd]

Well, the syntax to create unique keys in sql is:

ALTER TABLE `dw_order_items` ADD UNIQUE (
`item_id`,
`order_item`
)

This creates a UNIQUE restriction on the combination of those two fields. You can also restrict single fields.

UNIQUE restrictions make sure that you never run into any problems with duplicates. Quite useful.

But why would you want to restrict an item to only being in an order once??? Better if you use the primary key, order_item_id in the WHERE criteria.