How do I set default value for a foreign key column in mysql

Question

                       table1                    

            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


                    table2

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

Am having table1 and table2. In the table1 bookid is primary key and the table2 bookid is foreign key. I want to set table2 bookid default value to 0.

Is there any possibilities? We tried with default value as zero.

It throws an exceptions "Cannot add or update child row: a foreign key constaint fails"

Answer 1

I just ran this on mysql...

create table table1 (
    bookid int not null primary key,
    bookname varchar(100) not null,
    author varchar(100) not null
);

create table table2 (
    userid int not null,
    username varchar(100) not null,
    bookid int not null default 0
);
alter table table2 add constraint fk1 foreign key (bookid) references table1(bookid);

insert into table1(bookid,bookname,author) values (0,'None','none');

insert into table2(userid, username) values (1,'bob');

select * from table2;

Result

1    bob    0

You could also make the fk column table2.bookid nullable (bookid int null,). The choice to allow foreign keys to be null or use 'sentinel values' is a design choice best made consciously, if you're going to build a lot of code.