MYSQL 1452 Error

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.
James GatleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FishMongerCommented:
Due to the foreign key constraint, the usernames must exist in the Login table before you can add them to the Historical table.
Steve BinkCommented:
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.
James GatleeAuthor 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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Steve BinkCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James GatleeAuthor 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.
arnoldCommented:
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.
James GatleeAuthor Commented:
Relative Issue solved. Thank you for your contributions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.