tags:

views:

99

answers:

2

I'm doing the Agile Yii book.

Anyway, I'm trying to execute this command:

INSERT INTO tbl_project_user_assignment (project_id, user_id) values ('1','1'), ('1','2');

And I get this error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`trackstar_dev`.`tbl_project_user_assignment`, CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE)

So.. I figure let's see if tbl_project table have project_id=1. Did a quick SELECT * FROM tbl_project; and the project exist.

Ok then let's just check the user, SELECT * FROM tbl_user; Yup 2 user with id 1 and 2.

What am I doing wrong? Is there a typo? The agile yii book have several typos but they're not as serious and it's too new so there's no errata reported (checked already).

Here's the database schema from the source code:

-- Disable foreign keys
SET FOREIGN_KEY_CHECKS = 0 ;

-- Create tables section -------------------------------------------------

-- Table tbl_project

CREATE TABLE IF NOT EXISTS `tbl_project` (
  `id` INTEGER NOT NULL auto_increment,
  `name` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `create_time` DATETIME default NULL,
  `create_user_id` INTEGER default NULL,
  `update_time` DATETIME default NULL,
  `update_user_id` INTEGER default NULL,
  PRIMARY KEY  (`id`)
) ENGINE = InnoDB
;

-- DROP TABLE IF EXISTS `tbl_issue` ;

CREATE TABLE IF NOT EXISTS `tbl_issue` 
( 
  `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `description` varchar(2000), 
  `project_id` INTEGER,
  `type_id` INTEGER,
  `status_id` INTEGER,
  `owner_id` INTEGER,
  `requester_id` INTEGER,
  `create_time` DATETIME,
  `create_user_id` INTEGER,
  `update_time` DATETIME,
  `update_user_id` INTEGER  
) ENGINE = InnoDB
; 

-- DROP TABLE IF EXISTS `tbl_user` ;

-- Table User

CREATE TABLE IF NOT EXISTS `tbl_user` 
(
  `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `email` Varchar(256) NOT NULL,
  `username` Varchar(256),
  `password` Varchar(256),
  `last_login_time` Datetime,
  `create_time` DATETIME,
  `create_user_id` INTEGER,
  `update_time` DATETIME,
  `update_user_id` INTEGER
) ENGINE = InnoDB
; 

-- DROP TABLE IF EXISTS `tbl_project_user_assignment` ;

-- Table User

CREATE TABLE IF NOT EXISTS `tbl_project_user_assignment`
(
  `project_id` Int(11) NOT NULL,
  `user_id` Int(11) NOT NULL,
  `create_time` DATETIME,
  `create_user_id` INTEGER,
  `update_time` DATETIME,
  `update_user_id` INTEGER,
 PRIMARY KEY (`project_id`,`user_id`)
) ENGINE = InnoDB
;


-- The Relationships 
ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_project` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_owner` FOREIGN KEY (`owner_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; 

ALTER TABLE `tbl_issue` ADD CONSTRAINT `FK_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT; 

ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_project_user` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_project_user_assignment` ADD CONSTRAINT `FK_user_project` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;   

-- Insert some seed data so we can just begin using the database
INSERT INTO `tbl_user` 
  (`email`, `username`, `password`) 
VALUES 
  ('[email protected]','Test_User_One', MD5('test1')),
  ('[email protected]','Test_User_Two', MD5('test2'))    
;

-- Enable foreign keys
SET FOREIGN_KEY_CHECKS = 1 ;

Anyway, thanks in advance!

EDIT: Clarification that the project does indeed exist ^^.

mysql> select id,name from tbl_project;

+----+-------------------+

| id | name |

+----+-------------------+

| 6 | Project 1 |

| 1 | project zombied 1 |

+----+-------------------+

2 rows in set (0.00 sec)

+1  A: 

The project_id and user_id in tbl_project_user_assignment are typed as INT(11) rather than INTEGER. I'm inclined to think that INTEGER is 4 BYTES and INT(11) would go to 8 BYTES.

As commented above INTEGER fixes the problem.

MikeAinOz
A: 

This is a strange issue you have encountered, as well as an odd fix for it. As far as I am aware, there is no internal difference between INTEGER, INT, OR INT(XX) (where XX is some number) They are all the same datatype with the same byte storage allocation and min/max range. This should not play a role in MySQL evaluation of type mismatch for some fk relationships. My version/configuration of MySQL (5.1.49) does not throw the same constraint violation you are experiencing when given using INT(11) in one table and INTEGER in another. I wonder if this is somehow more related to your configuration or if you are using other external DB tools.

One can read more about the internals of MySQL datatype here:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

of particular interest on this page:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.) The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three characters are displayed using more than three characters.

jefftulsa
Just a regular Ubuntu box, Server version: 5.1.41-3ubuntu12.6 (Ubuntu). I guess they fixed it in 5.1.49?
mythicalprogrammer