MYSQL 1452 Error

James Gatlee
James Gatlee used Ask the Experts™
on
Hello Experts,

I am having issue with .MYSQL file not updating in sync with a parent table class. The code is as following:

Create table Login
(Username varchar(15)NOT NULL,
Firstname char(10) NOT NULL,
Lastname char(10) NOT NULL,
Password varchar(15) NOT NULL,
email varchar(25) NOT NULL,
PRIMARY KEY(Username));

INSERT into Login VALUES ('jason44','Jason','Labor','12345','jason@gmail.com');
INSERT into Login VALUES ('carol39','carol','spice','qwerty','carol@gmail.com');
INSERT into Login VALUES ('jennifer89','jennifer','Cicco','asdfg','jennifer@gmail.com');
INSERT into Login VALUES ('michael27','michael','lutherl','0987654','luther@gmail.com');

Open in new window


This is the synced table below:

create table Historical
(OrderNumber int(100) auto_increment,
Username varchar(15)NOT NULL,
CustomerType char(6),
NumberTickets int(3),
TicketCost float(5,2),
CreditCard int(16),
PRIMARY KEY(OrderNumber),
FOREIGN KEY(Username)REFERENCES Login(Username)
);
INSERT into Historical VALUES (NULL,'jason','Adult',2,7.00,9876543);
INSERT into Historical VALUES (NULL,'carol87','Adult',1,7.00,9876543);
INSERT into Historical VALUES (NULL,'jennifer1234','Adult',3,7.00,1828288);
INSERT into Historical VALUES (NULL,'michael987','Child',2,7.00,1828288);

Open in new window


The  error is as follows:
"ERROR 1452: Cannot add or update a child row: a foreign key constraint fails"

When attempting to insert new rows or values into the child table, this error occurs. While the file runs, it cannot be made updates. I am currently using only the cmd line of MySQL for this issue.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Due to the foreign key constraint, the usernames must exist in the Login table before you can add them to the Historical table.
Top Expert 2004

Commented:
As FIshMonger explained, but you need to re-evaluate your structure.  Using a varchar field as a foreign key is bound to lead to issues, including the one you are currently experiencing.  If you restructure to use a numeric key, it is much easier and faster to leverage the key properly.

Author

Commented:
Thanks for the response,

Not sure what is meant by username being in Login. From what I'm reading from the code Username is the destinated Primary Key, and is a Foreign key in the proceeding table.

is this referring to the 'null' data values in Login key with INSERT command? This data is supposed to auto_increment to keep track of the amount of registered users.

Login is supposed to be a Login-form. Auto-incrementing the username wouldn't make much since, but I am unsure what else could be substituted as the PrimaryKey. Unless changing the username from varchar to char would solve the current issue and potential feature issues?

*EDIT* I think I understand now experts. In other words, usernames will have to use the INSERT method first, THEN updated in the proceeding Historical table.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Top Expert 2004
Commented:
Your current definition:
Create table Login
(Username varchar(15)NOT NULL,
/*  [ ... ]  */
PRIMARY KEY(Username));
);

Open in new window

That is creating the table "Login", including a varchar field named Username, which is also the primary key.  That index will be based on 15 bytes of character data.  Alternatively, using an integer autonumber field provides for 4-8 bytes of numeric data.  A better approach for this table would be:
Create table Login
(UserID int NOT NULL AUTO_INCREMENT,
Username varchar(15)NOT NULL,
/*  [ ... ]  */
PRIMARY KEY(Username));
);

Open in new window


Regarding your foreign key, here is your current table:
create table Historical
(OrderNumber int(100) auto_increment,
Username varchar(15)NOT NULL,
/* [ ... ] */
PRIMARY KEY(OrderNumber),
FOREIGN KEY(Username)REFERENCES Login(Username)
);

Open in new window

This says that the field "Username" in table "Historical" will have a foreign key relationship with the field "Username" from the table "Login".  In other words, it will dependent on values found in Login.Username.  That means that any possible value for Historical.Username must already exist uniquely in Login.Username.  The error you are receiving indicates you are attempting to insert a value into Historical.Username which does not exist in Login.Username.

An excerpt of your sample data:
INSERT into Login VALUES ('jason44','Jason','Labor','12345','jason@gmail.com');
INSERT into Historical VALUES (NULL,'jason','Adult',2,7.00,9876543);

Open in new window

The values provided for the Username field do not match.  The key relationship must be an exact match (potentially case-insensitive).  At the least, you will need to audit your data for appropriate values.

If you decide to use the auto-number approach I described above, "Historical" will also need to change:
create table Historical
(OrderNumber int(100) auto_increment,
    UserID int NOT NULL,
CustomerType char(6),
NumberTickets int(3),
TicketCost float(5,2),
CreditCard int(16),
PRIMARY KEY(OrderNumber),
FOREIGN KEY(UserID)REFERENCES Login(UserID)
);

Open in new window

For existing applications, this would be a significant change, requiring thorough planning and testing.

Author

Commented:
Hello Expert,

Thank you for your thorough response. From what I am interpreting about the exact match values means that the current "NULL" value in historical must be replaced with the inserted VALUES such as 'jason44' for the first data field.

While it would make much more logical sense to have a userID as the primary key, instead of username(as username can possibly be deleted, modified, change, or even 'available' to other users), such key was made due to reflect a current login-form that was created in HTML.

I however, will take your advance and will attempt to change the current database we have to suit these measures.
Distinguished Expert 2017

Commented:
It seems null us being added to the auto_increment column.

Point being you have to check whether the username exists in the login table and if not insert a row into login table before the insert of this record as the prior commenters pointed out.

The error is because of a typo, the login table has jason34 , while the historical has jason as the user of the first insert record

The insert into shoukd list the columns excluding the auto increment one.

Author

Commented:
Relative Issue solved. Thank you for your contributions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial