Foreign key referential integrity keeping me from inserting records. Help!

[istrika]

Hi all,

I'm having some trouble inserting records into my MySQL db because of how I've set up my foreign keys. The problem is, I don't know how to fix it to make it do what I need it to.

Here is my schema:
http://imgur.com/t5bVt

Here is my php code to run the queries:

    // Add new email, clean it first
    $sql = 'insert into customers set email = :email';
    $s = $pdo -> prepare($sql);
    $s -> bindValue(':email', $_POST['email']);
    $s -> execute();

    // Get id of new customer
    $custid = $pdo -> lastInsertId();

    // Create new order for customer
    $sql = 'insert into orderinfo set 
        fk_id = "$custid",
        code = 9,
        orderdate = CURDATE()';
    $pdo -> exec($sql); 

    // Get id of new order
    $orderid = $pdo -> lastInsertId();

That's all I have so far because it gives me this error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (testing.orderinfo, CONSTRAINT orderinfo_ibfk_3 FOREIGN KEY (fk_id) REFERENCES customers (id))' in /var/www/ordering/index.php:54 Stack trace: #0 /var/www/ordering/index.php(54): PDO->exec('insert into ord...') #1 {main} thrown in /var/www/ordering/index.php on line 54

Here is how the tables in question are constructed:

  CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 |

  CREATE TABLE `orderinfo` (
  `orderno` bigint(20) NOT NULL AUTO_INCREMENT,
  `fk_id` int(11) NOT NULL,
  `code` tinyint(4) NOT NULL,
  `orderdate` date NOT NULL,
  `completed` date DEFAULT NULL,
  `photos` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`orderno`),
  KEY `code` (`code`),
  KEY `fk_id` (`fk_id`),
  CONSTRAINT `orderinfo_ibfk_3` FOREIGN KEY (`fk_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `orderinfo_ibfk_2` FOREIGN KEY (`code`) REFERENCES `status` (`code`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

I don't want orders to be created without having an associated customer id. My php code creates a customer id and attempts to store it in the proper field in the orderinfo table but I'm just getting the above error about referential integrity. I do want to keep referential integrity, but it's too restrictive right now.

How do I rectify this?

Thanks!

[istrika]

So I tried playing around with the foreign keys some. I dropped the keys then re-added them with ON DELETE CASCADE ON UPDATE CASCADE provisions but it did not help.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (testing.orderinfo, CONSTRAINT orderinfo_ibfk_3 FOREIGN KEY (fk_id) REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE)' in /var/www/ordering/index.php:54 Stack trace: #0 /var/www/ordering/index.php(54): PDO->exec('insert into ord...') #1 {main} thrown in /var/www/ordering/index.php on line 54

Any assistance would be greatly appreciated.

[istrika]

Figured it out. It turns out the fk_id = "$custid" line was not being parsed by SQL correctly. I don't know what value it was reading from it, but I managed to work around it by using a bindvalue procedure like I did earlier in the code.