Description:
I have 3 tables, t1, t2, t3. t3 has tow columns which are foreign keys to t1(id), t2(id). When I insert a new row via the QB by editing (not using SQL insert) I need to click twice on apply changes. The 1st time I get an error
How to repeat:
I wrote down the repeatition process and the I give you the sample database I worked on
After clicking the database ,and the on table t3, to show the contents of the table
1) Click Edit
2) write an appropriate value. (for the sample database, lets say 2,14)
3) click Applay Changes
Then I get : Cannot add or update a child row: a foreign key constraint fails
4) Change nothing
5) Click Applay Changes again
6) changes did take effect
If I use the command prompt or if I use QB's "SQL Query area" it works fine.
I give you the sample database I used with sample data (got it via mysqldump):
-- MySQL dump 10.9
--
-- Host: localhost Database: temp1
-- ------------------------------------------------------
-- Server version 4.1.10-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL auto_increment,
`col2` varchar(45) NOT NULL default '',
`col3` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `t1`
--
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (1,'abc',123),(2,'abc',45),(3,'abc',21),(4,'zxc',55),(5,'asd',55),(6,'abc',45);
UNLOCK TABLES;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
--
-- Table structure for table `t2`
--
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` varchar(45) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `t2`
--
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
LOCK TABLES `t2` WRITE;
INSERT INTO `t2` VALUES (11,'hello'),(12,'goodnight'),(13,'bye'),(14,'hoho');
UNLOCK TABLES;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
--
-- Table structure for table `t3`
--
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
`col1_from_t1` int(10) unsigned NOT NULL default '0',
`col2_from_t2` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`col1_from_t1`,`col2_from_t2`),
KEY `FK_t3_2` (`col2_from_t2`),
CONSTRAINT `FK_t3_2` FOREIGN KEY (`col2_from_t2`) REFERENCES `t2` (`id`),
CONSTRAINT `FK_t3_1` FOREIGN KEY (`col1_from_t1`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `t3`
--
/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
LOCK TABLES `t3` WRITE;
INSERT INTO `t3` VALUES (1,11),(6,11),(1,12),(5,12),(6,12),(1,13),(5,13),(6,13);
UNLOCK TABLES;
/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Suggested fix:
Click only once and get no error
