Solved

Explain foreign key constraints in MySQL

Posted on 2013-12-23
6
707 Views
Last Modified: 2013-12-24
I have previously always built databases by hand, but recently discovered the MySQL Workbench, which is a handy little tool.

Unfortunately, it is adding foreign key constraints to my database relationships, which is something I have never used before.

I am getting this error on an INSERT command:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`bugreport`.`projects_has_workers`, CONSTRAINT `fk_projects_has_workers_workers1` FOREIGN KEY (`workers_workers_id`) REFERENCES `workers` (`workers_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Can someone explain to me what this means? I understand that the database is upset that a foreign key is reference, and it probably thinks that something doesn't exist where it should, but I just don't quite understand it.

Thanks!
0
Comment
Question by:DrDamnit
  • 3
  • 2
6 Comments
 
LVL 32

Author Comment

by:DrDamnit
ID: 39737254
I think I figured it out. It's mad because there is nothing in the workers table, and a record in the workers table must exists prior to my being able to build a relationship between the project table and the workers table. Right?
0
 
LVL 9

Expert Comment

by:gt2847c
ID: 39737277
The foreign key constraint requires the existing of a matching value in the referenced table before it will allow a row to be inserted.  Just guessing based on how the error reads, it may be that the constraint is not doing what you meant it to do.  The constraint is complaining that there is no worker_id in the workers table matching the row you are attempting to insert.  It looks like you're attempting to make sure that a project has workers, but that's not quite what the constraint is attempting to enforce.

I could be reading a whole lot more into that error message than what you meant, so I may have misinterpreted it...
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39737370
I am pretty sure that it was complaining because the workers table was completely empty, and the ID I was attempting to pass was not a workers.workers_id value, but (in fact) was a users.users_id value.

Would that throw the error?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 9

Accepted Solution

by:
gt2847c earned 500 total points
ID: 39737427
The constraint mentioned in the error you posted lists the worker_id from the workers table.  An empty workers table would prevent you from inserting any records in the table where your constraint was applied.
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 39737434
Please, how have you created the table? I'm use MySqlWorkbench somtimes but it never created nothing if I didn't tell it to do :).

When you create a new table you have to specify columns names and properties. Below the blank background screen where you set columns properties there are 6 tabs:

1. Columns
2. Indexes
3. Foreign Keys
4. Triggers
5. Partitioning
6. Options

If you press F1 where one of these tabs is selected (or when a control within each tab is selected) yopu'll get the built-in help which will explain all you need to know. But the imnportant thing is that if you don't set manually a foreign key, the program doesn't create one for you, so you have to think about how you have created your tables because you can simply avoid the foreign key if you don't need it.

Best
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39737482
@marqusG:

I created the table using the database diagram editor. To create a one-to-one relationship, I press '1' on the keyboard and connect the tables. To create a projects_has_workers table, I used the 5 key. It is creating the constraints automatically.

Here is the table definition as created by workbench:
CREATE TABLE `workers_has_skilltags` (
  `workers_workers_id` int(11) NOT NULL,
  `skilltags_skilltags_id` int(11) NOT NULL,
  PRIMARY KEY (`workers_workers_id`,`skilltags_skilltags_id`),
  KEY `fk_workers_has_skilltags_skilltags1_idx` (`skilltags_skilltags_id`),
  KEY `fk_workers_has_skilltags_workers1_idx` (`workers_workers_id`),
  CONSTRAINT `fk_workers_has_skilltags_skilltags1` FOREIGN KEY (`skilltags_skilltags_id`) REFERENCES `skilltags` (`skilltags_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_workers_has_skilltags_workers1` FOREIGN KEY (`workers_workers_id`) REFERENCES `workers` (`workers_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window


I have just confirmed that the problem was user error on my part - once I started adding values to the tables, the errors go away!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Not needed 13 55
PHP loop not working 4 32
while loop in html mail format 5 32
masking php string 4 0
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now