update foreign key reference with default value on delete

Question

I m currently making a table on primary foreign key relation....

                       book table                    

                bookid     bookname         author
                 0         No book           no author
                 1         Engg Maths        Greiwal
                 2         Java Basics       James Gosling
                 3         Mahabharata       Ved Vyasa
                 4         Ramayana          Valmiki
                 5         Harry Potter      JK Rowling




                        usertable

               userid       name           bookid
                1          Arjun             2
                2          Charles           2
                3          Babbage           3

Here bookid is the foreign key in usertable and is primary key in booktable..... Now i've heard about cascading, setnull , restrict and no action constraints....

0 is set as the default value for column bookid in usertable.....

Now i want a situation when a bookid(primary) entry is deleted from the main table i.e booktable, the entires containing bookid in the user table should be updated to the default value ( i.e here 0 ).....

does there exist any method...???

all i can tell is that i m working on phpmyadmin mysql innodb......

score 2 · Answer 1 · answered Jul 01 '12 at 21:30

There's no built-in way, however there is a workaround. You can use a DELETE trigger to accomplish this:

DELIMITER $$
CREATE TRIGGER `book_delete` BEFORE DELETE ON `booktable`
  FOR EACH ROW BEGIN
    UPDATE `usertable` SET `bookid` = 0 WHERE `bookid` = OLD.`bookid`;
  END $$
DELIMITER ;

However, note that 0 is not null, so if you have any referential integrity constraints on usertalbe (that is, if you have bookid set as a FOREIGN KEY column), this will throw an error.

As a best practice, you really should use NULL to mean that the row in the usertable doesn't have a corresponding row in the booktable.

score 1 · Answer 2 · answered Jul 01 '12 at 21:40

With MySQL you can use ON DELETE SET NULL on the foreign key.

For instance:

CREATE TABLE t1 (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 title VARCHAR(256)
) ENGINE=InnoDB;

CREATE TABLE t2 (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 parent_id INT UNSIGNED, 
 INDEX(parent_id),
 FOREIGN KEY (parent_id) REFERENCES t1(id) ON DELETE SET NULL
) ENGINE=InnoDB;

insert into t2 values (null, 3);
insert into t2 values (null, 2);

This will result in table t2 looking like:

+----+-----------+
| id | parent_id |
+----+-----------+
|  2 |         2 |
|  1 |         3 |
+----+-----------+

Deleting a row from t1 as follows:

delete from t1 where id = 2;

Will result in:

+----+-----------+
| id | parent_id |
+----+-----------+
|  2 |      NULL |
|  1 |         3 |
+----+-----------+

Interesting enough, MySQL will parse an ON DELETE SET DEFAULT clause - but the table creation will fail.

So the short answer - you can set to NULL on delete - but currently there's no way to set the default value for the column on delete AND maintain the foreign key constraint.

update foreign key reference with default value on delete

2 Answers2