Tri Nguyen Tri Nguyen - 17 days ago 7
MySQL Question

MySQL - add data into 2 tables and 1 has foreign key

I'm a totally MySQL newcomer. Sr if my question is quite obvious. I got 2 tables

CREATE TABLE tbl_addresses(
PK_ADDRESS_ID int NOT NULL AUTO_INCREMENT,
house_number int NOT NULL,
street varchar(35),
district varchar(35),
city varchar(35),
postcode varchar(8),
PRIMARY KEY (PK_ADDRESS_ID)
);

CREATE TABLE tbl_people(
PK_PERSON_ID int NOT NULL AUTO_INCREMENT,
title varchar(6) NOT NULL, # Master / Mister therefor 6 is max
forename varchar(35) NOT NULL,
surname varchar(35) NOT NULL,
date_of_birth DATE NOT NULL,
contact_number varchar(12) NOT NULL,
FK_ADDRESS_ID int NOT NULL,
PRIMARY KEY (PK_PERSON_ID),
FOREIGN KEY (FK_ADDRESS_ID) REFERENCES tbl_addresses (PK_ADDRESS_ID)
);


and I'm trying to import data into these tables from Java using below syntaxes

INSERT INTO tbl_addresses (house_number,street,district,city,postcode) VALUES ('1','abc','','abc','abc');

INSERT INTO tbl_people (title,forename,surname,date_of_birth,contact_number) VALUES ('Mr','Tri ','Nguyen','1991-1-1','0123456789');


I got an error
Field 'FK_ADDRESS_ID'doesn't have a default value
and data actually goes into
tbl_addresses
but not
tbl_people
. Am I missing anything? Thanks in advance!

Answer

This error is being caused by that you labelled the FK_ADDRESS_ID field in the tbl_people table as NOT NULL, yet you are trying to do an INSERT without specifying a value for this column.

So something like this would work without error:

INSERT INTO tbl_people (title, forename, surname, date_of_birth,
                        contact_number, FK_ADDRESS_ID)
VALUES ('Mr', 'Tri', 'Nguyen', '1991-1-1', '0123456789', 1);

You could also specify a default value for FK_ADDRESS_ID (the error message you got alluded to this). Here is how you could adda default value:

ALTER TABLE tbl_people MODIFY COLUMN FK_ADDRESS_ID int NOT NULL DEFAULT 1

But because FK_ADDRESS_ID is a key into another table, the value should really be based on the primary key in tbl_addresses.

Comments