外键可以防止在mysql中删除数据

Question

I'm designing a website of book library for my project in php.

Anyways I have three tables named books,users and savebook.

Table book has following columns: "bookid", "title","author" "genre" and "summary".

And table users has following columns userid, username, password, and name.

Users can save books as favorites and those saved books are saved in the table named savebook with columns bookid and userid which are foreign key to table book and users

I used following query for that:

ALTER TABLE savebook
ADD CONSTRAINT bkid_usid
FOREIGN KEY (bookid)
REFERENCES books (bookid);

and

ALTER TABLE savebook
ADD CONSTRAINT usid_bkid
FOREIGN KEY (userid)
REFERENCES users(userid);

Now the problem is whenever i try to delete a book from table book using query

DELETE FROM books
WHERE bookid=1;

I get this message:

1451 - Cannot delete or update a parent row: a foreign key constraint fails (booklibrary.savebook, CONSTRAINT bkid_usid FOREIGN KEY (bookid) REFERENCES books (bookid))

How do i delete a book from table book which also deletes the related row in table savebook?

Answer 1

ALTER TABLE savebook
ADD CONSTRAINT bkid_usid
FOREIGN KEY (bookid)
REFERENCES books (bookid)
ON DELETE CASCADE
;

Make note of that last line I added to the ALTER from the original question, it is an optional part of the foreign key definition (there is also ON UPDATE ...). By default, when not specified, I believe MySQL treats it as NO ACTION or RESTRICT (those two are effectively the same as far as I know) instead of CASCADE. Full documentation found at http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html