cannot creat relationships

Yii 1.1 General Discussion
1

Hi, i’m using the book Agile Web Application Development with Yii1.1 and PHP5. it tells me to create the relationships in for my database tables, but i think there’s something wrong with the SQL statements, The statements from the book are:

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;

when i run this in MySQL(MySQL version 3.2.0.1) i get this error:

SQL query:

ALTER TABLE ‘tbl_issue’ ADD CONSTRAINT ‘FK_issue_project’ FOREIGN KEY ( ‘project_id’ ) REFERENCES ‘tbl_project’( ‘id’ ) ON DELETE CASCADE ON UPDATE RESTRICT ;

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘tbl_issue’ ADD CONSTRAINT ‘FK_issue_project’ FOREIGN KEY

(‘project_id’) REFERE’ at line 1

Does any1 knows what’s wrong here?

2

First of all, I 100 times more confortable (and safe) to create relationship with some graphical interface like phpmyadmin.

You have to click on ‘show relations’ and then add a foreign key.

About this error, I think that you have to refer to the name of table or fields with this quotation marks [``] and not with this [’’]. Is also possible to do without quotation marks at all.

3

I am also experiencing this. I have even used a graphical editor to come up with alter tables sql… yet it wont execute.





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  `trackstar_dev`.`tbl_issue` 

	ADD CONSTRAINT `FK_issue_owner` 

	FOREIGN KEY 	(`owner_id`) 

	REFERENCES  `trackstar_dev`.`tbl_user` (`id`) 

	ON DELETE CASCADE 

	ON UPDATE RESTRICT;


ALTER TABLE  `trackstar_dev`.`tbl_issue` 

	ADD CONSTRAINT `FK_issue_requester` 

	FOREIGN 	KEY (`requester_id`) 

	REFERENCES  `trackstar_dev`.`tbl_user` (`id`) 

	ON DELETE CASCADE

	ON 	UPDATE RESTRICT;


ALTER TABLE  `trackstar_dev`.`tbl_project_user_assignment` 

	ADD CONSTRAINT `FK_project_	user` 

	FOREIGN KEY (`project_id`) 

	REFERENCES  `trackstar_dev`.`tbl_project` (`id`) 

	ON	DELETE CASCADE 

	ON UPDATE RESTRICT;


ALTER TABLE  `trackstar_dev`.`tbl_project_user_assignment` 

	ADD CONSTRAINT `FK_user_project` 

	FOREIGN KEY (`user_id`) 

	REFERENCES  `trackstar_dev`.`tbl_user` (`id`) 

	ON DELETE CASCADE 

	ON UPDATE RESTRICT;

And i get this error:

#1005 - Can’t create table ‘trackstar_dev.#sql-128_273’ (errno: 150)

Please help.

4

The best I can come up with is to re-create the tables again. I did this graphically in MySQL Workbench, exported the SQL create. HOWEVER: it wouldn’t execute. I had to remove DEFAULT NULL from project_id statement as this was illegal.

This code now executes.

I would still like to know WHY I couldn’t ALTER TABLES?!?!


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';


DROP SCHEMA IF EXISTS `trackstar_dev` ;

CREATE SCHEMA IF NOT EXISTS `trackstar_dev` DEFAULT CHARACTER SET latin1 ;

USE `trackstar_dev` ;


-- -----------------------------------------------------

-- Table `trackstar_dev`.`tbl_project`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `trackstar_dev`.`tbl_project` ;


CREATE  TABLE IF NOT EXISTS `trackstar_dev`.`tbl_project` (

  `id` INT(11) NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(128) NULL DEFAULT NULL ,

  `description` TEXT NULL DEFAULT NULL ,

  `create_time` DATETIME NULL DEFAULT NULL ,

  `create_user_id` INT(11) NULL DEFAULT NULL ,

  `update_time` DATETIME NULL DEFAULT NULL ,

  `update_user_id` INT(11) NULL DEFAULT NULL ,

  PRIMARY KEY (`id`) )

ENGINE = MyISAM

AUTO_INCREMENT = 8

DEFAULT CHARACTER SET = latin1;




-- -----------------------------------------------------

-- Table `trackstar_dev`.`tbl_user`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `trackstar_dev`.`tbl_user` ;


CREATE  TABLE IF NOT EXISTS `trackstar_dev`.`tbl_user` (

  `id` INT(11) NOT NULL AUTO_INCREMENT ,

  `email` VARCHAR(256) NOT NULL ,

  `username` VARCHAR(256) NULL DEFAULT NULL ,

  `password` VARCHAR(256) NULL DEFAULT NULL ,

  `last_login_time` DATETIME NULL DEFAULT NULL ,

  `create_time` DATETIME NULL DEFAULT NULL ,

  `create_user_id` INT(11) NULL DEFAULT NULL ,

  `update_time` DATETIME NULL DEFAULT NULL ,

  `update_user_id` INT(11) NULL DEFAULT NULL ,

  PRIMARY KEY (`id`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;




-- -----------------------------------------------------

-- Table `trackstar_dev`.`tbl_issue` 

-- Note: DEFAULT NULL removed from `project_id` statement as this was illegal.

-- -----------------------------------------------------

DROP TABLE IF EXISTS `trackstar_dev`.`tbl_issue` ;


CREATE  TABLE IF NOT EXISTS `trackstar_dev`.`tbl_issue` (

  `id` INT(11) NOT NULL AUTO_INCREMENT ,

  `name` VARCHAR(256) NOT NULL ,

  `description` VARCHAR(2000) NULL DEFAULT NULL ,

  `project_id` INT(11) NOT NULL  ,

  `type_id` INT(11) NULL DEFAULT NULL ,

  `status_id` INT(11) NULL DEFAULT NULL ,

  `owner_id` INT(11) NULL DEFAULT NULL ,

  `requester_id` INT(11) NULL DEFAULT NULL ,

  `create_time` DATETIME NULL DEFAULT NULL ,

  `create_user_id` INT(11) NULL DEFAULT NULL ,

  `update_time` DATETIME NULL DEFAULT NULL ,

  `update_user_id` INT(11) NULL DEFAULT NULL ,

  PRIMARY KEY (`id`, `project_id`) ,

  INDEX `fk_issue_project` (`project_id` ASC) ,

  INDEX `fk_issue_owner` (`owner_id` ASC) ,

  INDEX `fk_issue_requester` (`requester_id` ASC) ,

  CONSTRAINT `fk_issue_project`

    FOREIGN KEY (`project_id` )

    REFERENCES `trackstar_dev`.`tbl_project` (`id` )

    ON DELETE CASCADE

    ON UPDATE RESTRICT,

  CONSTRAINT `fk_issue_owner`

    FOREIGN KEY (`owner_id` )

    REFERENCES `trackstar_dev`.`tbl_user` (`id` )

    ON DELETE CASCADE

    ON UPDATE RESTRICT,

  CONSTRAINT `fk_issue_requester`

    FOREIGN KEY (`requester_id` )

    REFERENCES `trackstar_dev`.`tbl_user` (`id` )

    ON DELETE CASCADE

    ON UPDATE RESTRICT)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;




-- -----------------------------------------------------

-- Table `trackstar_dev`.`tbl_project_user_assignment`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `trackstar_dev`.`tbl_project_user_assignment` ;


CREATE  TABLE IF NOT EXISTS `trackstar_dev`.`tbl_project_user_assignment` (

  `project_id` INT(11) NOT NULL ,

  `user_id` INT(11) NOT NULL ,

  `create_time` DATETIME NULL DEFAULT NULL ,

  `create_user_id` INT(11) NULL DEFAULT NULL ,

  `update_time` DATETIME NULL DEFAULT NULL ,

  `update_user_id` INT(11) NULL DEFAULT NULL ,

  PRIMARY KEY (`project_id`, `user_id`) ,

  INDEX `user_id` (`user_id` ASC) ,

  INDEX `fk_project_user` (`project_id` ASC) ,

  CONSTRAINT `fk_project_user`

    FOREIGN KEY (`project_id` )

    REFERENCES `trackstar_dev`.`tbl_project` (`id` )

    ON DELETE CASCADE

    ON UPDATE RESTRICT,

  CONSTRAINT `fk_user_project`

    FOREIGN KEY (`user_id` )

    REFERENCES `trackstar_dev`.`tbl_user` (`id` )

    ON DELETE CASCADE

    ON UPDATE RESTRICT)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1;





SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
5

Anyone use Mysql workbench? The easiest way is to create the graphic scheme, and then workbench can generate SQL with foreign keys automatically.

6

On other issue, cannot seem to specify


ON UPDATE RESTRICT;

this disappears when I analyse the table after creation and if i try to add it, say with mysqladmin, it fails.

7

Just one more note:

if you were having trouble with the insert data as in the book on p97 too:


INSERT INTO `trackstar_dev`.`tbl_user` (`email`, `username`, `password`) VALUES ('test1@notanaddress.com', 'Test_User_One', 'MD5(\'test1\')');# 1 row(s) affected.


INSERT INTO `trackstar_dev`.`tbl_user` (`email`, `username`, `password`) VALUES ('test2@notanaddress.com', 'Test_User_Tw', 'MD5(\'test2\')');# 1 row(s) affected.
8

MD5 function will not work, you try to insert just a string

9

Hi,

as mentioned above use backticks (`) for fields and single-quotes (’) for values.

If you are using MySQL Workbench (WB) on OSX. I noticed a weird behaviour when using backticks and don’t understand it yet: After the opening backtick all invisible keys as arrow up, down etc and backspace appear in the text and mess-up the code. Think it is a bug. The only workarround here is to copy and paste the backtick.

Another thing I noticed, while copying the code from a pdf into WB, saving the file and opening it again, WB is complaining about inconsistent line endings. Check this too.

If anyone can find out about this above mentioned WB behaviour on OSX, please post here. I wanna fix this.

Cheers,

Peter

10

I posted this exact error on Stack Overflow and was told that the issue is because there are multiple fields with the same name in different tables (e.g. project_id and requester_id appear twice). If you want to use a field as a foreign key, it can only appear in one place.

The solution: rename the fields in the join table (tbl_project_user_assignment) to something like proj_id and req_id and update the relationship commands accordingly.

TTS

11

I’m not convinced by the stack overflow post - there is perhaps an issue with foreign key column name clashes, if there is such an issue in MySQL, but as far as I can see the foreign key constraint names themselves are unique (and I always make my FK names like tablename_FKcolumn_fk_constraint so as to make them unique.

I have had various error 1005 issues that I have not managed to resolve.

One problem was my own mistake, when I created columns using ‘NOT NULL’, then tried to create a foreign key that ‘SET NULL’ ON DELETE, which gave me the 1005 error, but is obviously (correctly) because I cannot have a foreign key that wants to set a column to null that cannot be null.

This being said, I have had the same problems when trying to create other foreign keys that just cascaded, without any obvious reason for it, and sometimes the only solution has been to delete the table in question (containing the foreign key) and then recreate it. I generally use MySQL workbench, but have also used Query Browser and phpMyAdmin, so it would seem that the problem is not from the database client.

12

I too had all sorts of problems while trying to create relationships, described in the Agile Web Application Development.

I then followed advice to try and add relationships with phpmyadmin UI.

One of the relationships:




ALTER TABLE `tbl_issue` ADD FOREIGN KEY ( `project_id` ) REFERENCES `trackstar`.`tbl_project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;

Relationship was added, but not entirely. CASCADE value for ON UPDATE gets through with no errors, but doesn’t get set at all, value disappears every time. Setting it to NO ACTION however, works.

From mysql documentation:

Can someone with sufficient knowledge confirm, that NO ACTION is the right replacement for RESTRICT? I am concerned about how the delayed check influences a relationship logic in MySQL.

I am using InnoDB engine.

13

OK, I just read quote from MySQL docs again and realized that the last sentence basically answers my question - there is no delayed check in MySQL, so same rules apply. In future, I will try to read things thourougly!

14

The problem is simple:

You cannot create relationships between tables because tbl_project 's database engine is MyISAM, whereas you specify the database engine of the other 3 tables as InnoDB.

[b]

The solution is yet simpler:[/b]

In phpMyAdmin (if you use MySql), select the tbl_project table, select "Operations" tab and change the storage engine from MyISAM to InnoDB. Rerun the SQL query and voila!

15

I’m not sure exactly who you’re replying to, but I can’t see any explicit mention of the tables involved being MyISAM tables anyway, and in my case all my tables are InnoDB tables but I still get the same problem.

So while that might solve the problem for some people (if they have made that error), it still persists for others.

16

In addition, here are some additional troubleshooting tipps:

  • Field definitions have to match exactly. i.e. an UNSIGNED INT(13) can only reference another UNSIGNED INT(13). Especially mismatching DEFAULT NULL/NOT NULL definitions are a surefire way to shoot yourself in the foot.

  • The mentioned error 150 is an indicator that a constraint could not be created because your dataset fails to satisfy that very constraint right now! This is a bit pesky to sort out as MySQL won’t point you directly at the offending record(s). Running a few JOINs might help you with this.

17

In the book example, the tbl_project table is created first, with no storage engine specified. That’s why, by default, it gets the MyISAM engine. Later on, the rest of 3 tables are created, with the InnoDB engine specified in the SQL Query.

I think that my above given solution will solve the problem for more than 50% of the people using MySQL and struggling with this problem. Better than nothing. Cheers!

18

Ok - I hadn’t realised you were referring specifically to the book example.

19

I had the same problem. The solution of changing the project table to use innodb worked perfectly. Thanks a lot!!!.

20

I had the same issue, and this solution worked for me.