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"

*We tried with default ....*. Please post sql query you tried. — Ravinder Reddy, Nov 19 '15 at 07:21
We are using SQLYog tool. In that alter table we have a column named Default. We change the values to zero and save. — Aoryouncellvan, Nov 19 '15 at 07:34
post results of `show create table1;` and `show create table2;` and `insert` statement on table2 that failed. — Ravinder Reddy, Nov 19 '15 at 07:37

score 4 · Answer 1 · answered Nov 19 '15 at 07:32

why do you have a row called "no book"? all you need in a table are books, not a "no book" first delete that useless row from table1, then in table2: allow the foreign key to be null, if the foreign key is null then that means "no book" basically, now "null" is the default value, and null means "no book" that is what you want

joshp · Accepted Answer · 2015-11-19T07:50:33.747

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.

It works for me too. But am asking the bookid in the table2 is a foreign key. so only it is not accepting the default values. — Aoryouncellvan, Nov 19 '15 at 07:33

score 0 · Answer 3 · answered Nov 19 '15 at 07:43

0

If I understood your problem,Possible two solution

Either table1 should have default row ,and table 2's default value will be referring to it
Allow NULL values to be foreign key

Allowing null values or default values-SQL fiddle

answered Nov 19 '15 at 07:43

rupesh_padhye

1,355
2
13
25

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

3 Answers3