1

I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have a default value. When I make the foreign key auto increment in MySQL then I can enter data, but it is not the way it should work. May be it can be acceptable for one to one relationship. Shouldn't the connected table get the value from Master table? or is there sth else I'm missing here?

I'd really appreciate any suggestions.

Have a nice day.

1 Answer 1

1

I'm not sure what you are saying here. Assume the following:

CREATE TABLE PARENT
( a int not null primary key );

CREATE TABLE CHILD
( b int not null primary key
, a int not null 
,    constraint fk foreign key (a) 
         references PARENT (a) );

insert into PARENT (a) values (1),(2);

-- What should the default value of a be?
insert into CHILD (b) values (1); 

A foreign key is a rule that makes sure that you use a valid value for the column(s) involved. If you want a default value for a in CHILD you can add another constraint (I'm not sure why default values are considered to be constraints):

CREATE TABLE CHILD
( b int not null primary key
, a int DEFAULT 1 not null 
,    constraint fk foreign key (a) 
         references PARENT (a) );

Without that it is impossible for the DBMS to know what you mean when you don't provide a value for an attribute.

EDIT: Add info on how to retrieve last generated value

If you want to inspect what was generated in the PARENT table, and use that value for the CHILD table, you can use a function LAST_INSERT_ID():

CREATE TABLE PARENT
( a int not null AUTO_INCREMENT primary key
, c int not null );

CREATE TABLE CHILD
( b int not null primary key
, a int DEFAULT 1 not null 
,    constraint fk foreign key (a) 
         references PARENT (a) );

insert into PARENT (c) values (1);
insert into CHILD (b,a) select 1, LAST_INSERT_ID();

or

insert into CHILD (b,a) values (2, LAST_INSERT_ID());
8
  • 1
    MySQL will silently ignore the foreign keys the way you defined them. You need to define them as a separate constraint, not inline with the column.
    – user1822
    Jul 25, 2015 at 12:02
  • The mysql tag was added after my post, but I'll update my answer Jul 25, 2015 at 12:05
  • I added the tag because the question stated MySQL ;)
    – user1822
    Jul 25, 2015 at 12:11
  • So you actually read the questions, perhaps I should try that too ;-) Jul 25, 2015 at 12:20
  • 1
    I've added info of LAST_INSERT_ID(), does that help? Jul 25, 2015 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.