-2

I have a big problem with this SQL error when I'm trying to insert a event in my database with this PDO.

SQL:

$subsquery1 = "SELECT cat_id FROM categories WHERE cat_name = ".$event_cat;

$subsquery2 = "SELECT tournament_id FROM tournaments WHERE tournament_name = ".$event_tournament;

$sqlx = "INSERT INTO events(event_team1, event_team2, event_cat, event_tournament, event_start_at, event_end_to, event_by) VALUES(:event_team1, :event_team2, :event_cat, :event_tournament, :event_start_at, :event_end_to, :event_by)";

// Prepare statement
$statementx = $pdo->prepare($sqlx);

// execute the query
$resultx = $statementx->execute(array(':event_team1' => $event_team1, ':event_team2' => $event_team2, ':event_cat'=> $subsquery1, ':event_tournament'=> $subsquery2, ':event_start_at' => $event_start_at, ':event_end_to' => $event_end_to,':event_by'=>$event_by));

SQL error:

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (datenbank.events, CONSTRAINT events_ibfk_1 FOREIGN KEY (event_cat) REFERENCES categories (cat_id) ON DELETE CASCADE ON UPDATE CASCADE)

-- Table structure for table `events`
CREATE TABLE `events` (
  `event_id` int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `event_team1` varchar(255) NOT NULL,
  `event_team2` varchar(255) NOT NULL,
  `event_start_at` timestamp NOT NULL,
  `event_end_to` timestamp NOT NULL,
  `event_cat` int(8) NOT NULL,
  `event_by` int(8) NOT NULL,
  `event_tournament` int(8) NOT NULL,
  KEY `event_cat` (`event_cat`),
  KEY `event_by` (`event_by`),
  KEY `event_tournament` (`event_tournament`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Constraints for table posts

ALTER TABLE `posts` ADD CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`post_event`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `posts_ibfk_2` FOREIGN KEY (`post_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE;

-- Constraints for table events

ALTER TABLE `events` ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`event_cat`) REFERENCES `categories` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`event_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `events_ibfk_3` FOREIGN KEY (`event_tournament`) REFERENCES `tournaments` (`tournament_id`) ON DELETE CASCADE ON UPDATE CASCADE;

What can I try to solve this?

halfer
  • 19,824
  • 17
  • 99
  • 186
Manifest Man
  • 873
  • 10
  • 16

1 Answers1

1

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (datenbank.events, CONSTRAINTevents_ibfk_1FOREIGN KEY (event_cat) REFERENCEScategories(cat_id)

You are performing an insert to table events with a value being placed in column event_cat.

That value does not already exist in table categories in the column cat_id.

And you said it must. So the db engine says it won't do it. It is faithfully obeying your orders.

Drew
  • 24,851
  • 10
  • 43
  • 78
  • Morning Drew, the variable in the pdo sure already exists in the table categories, that is why i did a subsquery for the three foreign keys, or i,m doing somethings wrong there ?... – Manifest Man Jul 25 '16 at 05:21
  • Your insert statement does not have the value that is proper. What you did in the select statements is irrelevant. When the insert comes along, that is the only thing that is doing any data modification. It could be an update or it could be an insert. You chose insert. If you need to do a var_dump then do that. – Drew Jul 25 '16 at 05:26
  • If you need to look at the general query log (what your server receives from you), you can do that [here](http://dev.mysql.com/doc/refman/5.7/en/query-log.html) ... here is what it looks like in action [here](http://stackoverflow.com/a/38394479) – Drew Jul 25 '16 at 05:30
  • Ok, you know more about Error 1452 than me apparently. – Drew Jul 25 '16 at 05:41
  • As already mentioned, i was implementing multi-transactions with pdo prepared statements and i got that fault everytime, then i changed all to MySQL multi-transactions, and it solved that problem :) Drew ... – Manifest Man Aug 01 '16 at 11:05