Chapter 8: SQLSTATE 23000 constraint violation role-name

Can anyone tell me what is wrong? I have turned on "ON DELETE CASCADE

ON UPDATE CASCADE" in the table. Here is the error message:

and mysql ddl:

project.php:




	/**

	 * creates an association between the project, the user and the user's role within the project

	 */

	public function associateUserToRole($role, $userId)

	{

		$sql = "INSERT INTO tbl_project_user_role (project_id, user_id, role) VALUES (:projectId, :userId, :role)";

		$command = Yii::app()->db->createCommand($sql);

		$command->bindValue(":projectId", $this->id, PDO::PARAM_INT);

		$command->bindValue(":userId", $userId, PDO::PARAM_INT);

		$command->bindValue(":role", $role, PDO::PARAM_STR);

		return $command->execute();

	}

	

	/**

	 * removes an association between the project, the user and the user's role within the project

	 */

	public function removeUserFromRole($role, $userId)

	{

		$sql = "DELETE FROM tbl_project_user_role WHERE project_id=:projectId AND user_id=:userId AND role=:role";

		$command = Yii::app()->db->createCommand($sql);

		$command->bindValue(":projectId", $this->id, PDO::PARAM_INT);

		$command->bindValue(":userId", $userId, PDO::PARAM_INT);

		$command->bindValue(":role", $role, PDO::PARAM_STR);

		return $command->execute(); 

	}



projecttest.php:




	public function testUserRoleAssignment()

	{

		$project = $this->projects('project1');

		$user = $this->users('user1');  

		$this->assertEquals(1,$project->associateUserToRole('owner', $user->id)); 

		$this->assertEquals(1,$project->removeUserFromRole('owner', $user->id));

	}



Can you verify you have a row in the table authitem corresponding to name = ‘owner’ in your TEST database? in mysql, using the test database, what do you get if you issue:

[sql]select * from authitem where name = ‘owner’;[/sql]

Thanks! I was doing the order incorrectly of the book. Before I executed the rbac command, I forget to put those three Auth* tables. After I put the tables into the database, and I didn’t execute rbac again. My bad, the error trigered by empty values of authitem.

Using rbac after creating the tables isn’t in the book as far as I can see (I have Kindle edition, if that makes a difference). Also, executing rbac doesn’t populate the table. It seems there is something missing in the book.

Hopefully I’ll find it in this forum. If so, I’ll update this post. I leave this here because this is the page google brings me to for this error- so presumably others will land here too.

EDIT: for anybody else on Kindle, this is location 3250 (and thereabouts). Everybody else talks about page numbers.

EDIT#2: I put in the CASCADE stuff as the OP did, but no joy. I ran and reran the rbac stuff and nothing was added to the db. Finally I simply entered ‘owner’ for the name in the first entry of AuthItem (and nothing else) and the test ran. I would love it if someone could clarify- are there steps missing in the book? What are the db tables supposed to look like after running the rbac? TIA.

I really wish I knew what those auth databases were supposed to be initialized to. I’m now getting


CException:  Unknown authorization item "member"

when trying to run testUserAccessBasedOnProjectRole in ProjectTests.php

Interesting thing is this exception is being thrown by CPHPAuthManager.php, but I’m using the databases with my tests. So I don’t even really understand how this error is happening.

It’s happening in the line


$auth->assign('member',$row1['user_id'],$bizRule); 

One thing to keep in mind is that these are hours and hours wasted on debugging the unit tests, not even on debugging the app.

Error Code: 1146. Table ‘trackstar_test.authitem’ doesn’t exist

This is on my trackstar_test db