mysql prevent deleting records that is in used

Question

hi,

i got 2 tables. product and order_items which contain all the products that were bought.

so how do i create a relationship in mysql whereby if a product exists in order_items, restrict users from deleting it from product table??

thanks

Answer 1

You're looking for a foreign key. Specifically look at the "Restrict" option.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Answer 2

You can do this with Foreign keys with the InnoDB Engine.

ALTER TABLE order_items ADD FOREIGN KEY (`p_id`) REFERENCES `products` (`p_id`);

The ID on products must be a key (it probably already is the primary key).

If you are not using InnoDB, you cannot enforce this with MySQL, but it must be enforced with your application (check whether a record exists in orders first for example).

So with your tables, you run:

ALTER TABLE `order_item` ADD FOREIGN KEY (`bookid`) REFERENCES `book` (`id`);

Answer 3

my table structure:

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `author` varchar(100) NOT NULL,
  `publisher` varchar(100) NOT NULL,
  `edition` int(11) NOT NULL,
  `isbn` varchar(13) NOT NULL,
  `category` varchar(11) NOT NULL,
  `datesubmitted` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

CREATE TABLE `order_item` (
  `orderid` int(11) NOT NULL,
  `bookid` int(11) NOT NULL,
  KEY `Foreign` (`bookid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;