Error 500 - Cdbexception - Cdbcommand Failed To Execute The Sql Statement: : Integrity Constraint Violation

Yii 1.1 General Discussion
1

I have two tables, AUTHORS and BOOK which have one-to-many relationship i.e. AUTHORS can have multiple BOOK. Data get inserted but when i call admin page, i receive the above error.

AUTHORS.php (Model)


public function relations()

    {

        // NOTE: you may need to adjust the relation name and the related

        // class name for the relations automatically generated below.

        return array(

            'book' => array(self::BELONGS_TO, 'Book', 'book_id'),);

}


public function search()

    {


        $criteria=new CDbCriteria;

        $criteria->compare('id',$this->id);

        $criteria->compare('name',$this->name,true);

        $criteria->together= true;


        $criteria->params=array('author.book_id=:book.id');


        return new CActiveDataProvider($this, array(

            'criteria'=>$criteria,

        ));

    }

Book.php (model)


public function relations()

    {

        return array(


            'authors' => array(self::HAS_MANY, 'Authors', 'book_id'),

        );

    }
2

Your relations are not set properly.

Books belongs to author not author to books.

In author model your relation should be:


'books'=>array(self::HAS_MANY, 'Book', 'book_id')

And in Book model your relation should be:


'author'=>array(self::BELONGS_TO, 'Authors', 'book_id')

I believe that I know why you got confused when you was making the relations, I will try to explain how you should do it.

In author model, you gave the name to your relation "book", and that made you think that you are making the relation for book model, but you are not, you are inside the Author model, so you are making the relations for Authors not Books. That name "book" will just refer to this relation, it is name/marker, call it however you want.

When you was creating the relations, you should forget the name of relations and think like this: "Author can have many books, so I have to refer to the Book model and specify the foreign key", and inside the author model you would do it with:


self::HAS_MANY, 'Book', 'book_id'

With this you are saying exactly that…"Hey, Author has many books, and the foreign key is book_id, that is what binds author and books.". Name "book" confused you.

Same way in Book model, you would say "Hey, book belongs to author, and foreign key is book_id", and inside book model you would write it with :


self::BELONGS_TO, 'Authors', 'book_id'

Name ‘author’ you will use when you want to “say” in your code: “Give me Book and its author”. Or if you need to get some author and his books, you would use this “books” name to “say” what you want in your code (“Give me Book and its author”, for example…).

I hope this helps to you.

Also, one note, it is convention to use singular names for models in Yii, so you shouldn’t call your model Authors, but “Author.”. Off course you don’t have to follow this convention, but it is a good practice to be consistent in your code, and that means if you use plural names, use them for all models/controllers/ whatever, don’t mix it up, you will make a mess :D.

Sorry for my bad English, it is not my native language.