mysql - 插入后 MySQL 出现 CDbCommand 失败错误

Question

我有一个用于组和成员的 MySQL 数据库。当我要在组模型中插入任何记录时，这里一切都很好，但是当我要在成员模型中插入任何数据时，它会显示这样的错误

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`tbl_groupapp`.`tbl_member`, CONSTRAINT `FK_member_group` FOREIGN KEY (`group_id`) REFERENCES `tbl_group` (`id`) ON DELETE CASCADE). The SQL statement executed was: INSERT INTO `tbl_member` (`firstname`, `lastname`, `gender`, `membersince`) VALUES (:yp0, :yp1, :yp2, :yp3) 

我不知道为什么会发生在这里。这是我的数据库架构

--
-- Table structure for table `tbl_group`
--

CREATE TABLE IF NOT EXISTS `tbl_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=53 ;

--
-- Dumping data for table `tbl_group`
--

INSERT INTO `tbl_group` (`id`, `name`) VALUES
(37, 'Test Group'),
(38, 'bdbsdsb'),
(39, 'ruieryei'),
(40, 'dbshdbs'),
(41, 'dbshdbs'),
(42, 'dbshdbs'),
(43, 'dbshdbs'),
(44, 'dbshdbs'),
(45, 'dbshdbs'),
(46, 'dbshdbs'),
(47, 'dbshdbs'),
(48, 'dbshdbs'),
(49, 'dbshdbs'),
(50, 'group name'),
(51, 'group name'),
(52, 'dbshdbs');

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

--
-- Table structure for table `tbl_member`
--

CREATE TABLE IF NOT EXISTS `tbl_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `firstname` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `lastname` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `gender` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `membersince` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_member_group` (`group_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=52 ;

--
-- Dumping data for table `tbl_member`
--

INSERT INTO `tbl_member` (`id`, `group_id`, `firstname`, `lastname`, `gender`, `membersince`) VALUES
(31, 37, 'First Name', 'Second Name', 'male', '2012-02-22 00:00:00'),
(32, 38, 'dsadsmadnas', 'jieo/uwoe', 'female', '2012-02-16 00:00:00'),
(33, 39, 'rerhejrgejh', 'nmbfdnfb,d', 'male', '2012-02-23 00:00:00'),
(34, 40, 'test group', 'test group', '', '0000-00-00 00:00:00'),
(35, 41, 'test group1', 'test group2', '', '0000-00-00 00:00:00'),
(36, 42, 'test group1', 'test group2', '', '0000-00-00 00:00:00'),
(37, 43, 'test group', 'test group2', '', '0000-00-00 00:00:00'),
(38, 44, 'test group1', 'test group2', '', '0000-00-00 00:00:00'),
(39, 45, 'test group', 'test group2', '', '0000-00-00 00:00:00'),
(40, 46, 'test group', 'test group2', '', '0000-00-00 00:00:00'),
(41, 47, 'test group1', 'test group2', 'male', '0000-00-00 00:00:00'),
(42, 48, 'test group1', 'test group2', 'm', '0000-00-00 00:00:00'),
(43, 49, 'test group1', 'test group2', 'Male', '0000-00-00 00:00:00'),
(44, 50, 'firstname ', 'secondname', 'Male', '0000-00-00 00:00:00'),
(45, 51, 'test group', 'test group', 'Male', '2012-02-28 00:00:00'),
(46, 52, 'test group', 'test group2', 'Male', '2012-02-15 00:00:00');

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

--
-- Constraints for table `tbl_member`
--
ALTER TABLE `tbl_member`
  ADD CONSTRAINT `FK_member_group` FOREIGN KEY (`group_id`) REFERENCES `tbl_group` (`id`) ON DELETE CASCADE;

任何帮助和建议都将非常感激。这是成员(member)的表格文件

<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(
  'id'=>'member-form',
  'enableAjaxValidation'=>false,
)); ?>

  <p class="note">Fields with <span class="required">*</span> are required.</p>

  <?php echo $form->errorSummary($model); ?>

  <div class="row">
    <?php echo $form->labelEx($model,'group_id'); ?>
    <?php echo $form->textField($model,'group_id'); ?>
    <?php echo $form->error($model,'group_id'); ?>
  </div>

  <div class="row">
    <?php echo $form->labelEx($model,'firstname'); ?>
    <?php echo $form->textField($model,'firstname',array('size'=>60,'maxlength'=>80)); ?>
    <?php echo $form->error($model,'firstname'); ?>
  </div>

  <div class="row">
    <?php echo $form->labelEx($model,'lastname'); ?>
    <?php echo $form->textField($model,'lastname',array('size'=>60,'maxlength'=>80)); ?>
    <?php echo $form->error($model,'lastname'); ?>
  </div>

  <div class="row">
    <?php echo $form->labelEx($model,'gender'); ?>
    <?php echo $form->textField($model,'gender',array('size'=>10,'maxlength'=>10)); ?>
    <?php echo $form->error($model,'gender'); ?>
  </div>

  <div class="row">
    <?php echo $form->labelEx($model,'membersince'); ?>
    <?php echo $form->textField($model,'membersince'); ?>
    <?php echo $form->error($model,'membersince'); ?>
  </div>

  <div class="row buttons">
    <?php echo CHtml::submitButton($model->isNewRecord ? 'Create' : 'Save'); ?>
  </div>

<?php $this->endWidget(); ?>

</div><!-- form -->

成员(member) Controller 代码

<?php

class MemberController extends Controller
{
  /**
   * @var string the default layout for the views. Defaults to '//layouts/column2', meaning
   * using two-column layout. See 'protected/views/layouts/column2.php'.
   */
  public $layout='//layouts/column2';

  /**
   * @return array action filters
   */
  public function filters()
  {
    return array(
      'accessControl', // perform access control for CRUD operations
    );
  }

  /**
   * Specifies the access control rules.
   * This method is used by the 'accessControl' filter.
   * @return array access control rules
   */
  public function accessRules()
  {
    return array(
      array('allow',  // allow all users to perform 'index' and 'view' actions
        'actions'=>array('index','view'),
        'users'=>array('*'),
      ),
      array('allow', // allow authenticated user to perform 'create' and 'update' actions
        'actions'=>array('create','update'),
        'users'=>array('@'),
      ),
      array('allow', // allow admin user to perform 'admin' and 'delete' actions
        'actions'=>array('admin','delete'),
        'users'=>array('admin'),
      ),
      array('deny',  // deny all users
        'users'=>array('*'),
      ),
    );
  }

  /**
   * Displays a particular model.
   * @param integer $id the ID of the model to be displayed
   */
  public function actionView($id)
  {
    $this->render('view',array(
      'model'=>$this->loadModel($id),
    ));
  }

  /**
   * Creates a new model.
   * If creation is successful, the browser will be redirected to the 'view' page.
   */
  public function actionCreate()
  {
    $model=new Member;

    // Uncomment the following line if AJAX validation is needed
    // $this->performAjaxValidation($model);

    if(isset($_POST['Member']))
    {
      $model->attributes=$_POST['Member'];
      if($model->save())
        $this->redirect(array('view','id'=>$model->id));
    }

    $this->render('create',array(
      'model'=>$model,
    ));
  }

  /**
   * Updates a particular model.
   * If update is successful, the browser will be redirected to the 'view' page.
   * @param integer $id the ID of the model to be updated
   */
  public function actionUpdate($id)
  {
    $model=$this->loadModel($id);

    // Uncomment the following line if AJAX validation is needed
    // $this->performAjaxValidation($model);

    if(isset($_POST['Member']))
    {
      $model->attributes=$_POST['Member'];
      if($model->save())
        $this->redirect(array('view','id'=>$model->id));
    }

    $this->render('update',array(
      'model'=>$model,
    ));
  }

  /**
   * Deletes a particular model.
   * If deletion is successful, the browser will be redirected to the 'admin' page.
   * @param integer $id the ID of the model to be deleted
   */
  public function actionDelete($id)
  {
    if(Yii::app()->request->isPostRequest)
    {
      // we only allow deletion via POST request
      $this->loadModel($id)->delete();

      // if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser
      if(!isset($_GET['ajax']))
        $this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('admin'));
    }
    else
      throw new CHttpException(400,'Invalid request. Please do not repeat this request again.');
  }

  /**
   * Lists all models.
   */
  public function actionIndex()
  {
    $dataProvider=new CActiveDataProvider('Member');
    $this->render('index',array(
      'dataProvider'=>$dataProvider,
    ));
  }

  /**
   * Manages all models.
   */
  public function actionAdmin()
  {
    $model=new Member('search');
    $model->unsetAttributes();  // clear any default values
    if(isset($_GET['Member']))
      $model->attributes=$_GET['Member'];

    $this->render('admin',array(
      'model'=>$model,
    ));
  }

  /**
   * Returns the data model based on the primary key given in the GET variable.
   * If the data model is not found, an HTTP exception will be raised.
   * @param integer the ID of the model to be loaded
   */
  public function loadModel($id)
  {
    $model=Member::model()->findByPk($id);
    if($model===null)
      throw new CHttpException(404,'The requested page does not exist.');
    return $model;
  }

  /**
   * Performs the AJAX validation.
   * @param CModel the model to be validated
   */
  protected function performAjaxValidation($model)
  {
    if(isset($_POST['ajax']) && $_POST['ajax']==='member-form')
    {
      echo CActiveForm::validate($model);
      Yii::app()->end();
    }
  }
}

Answer 1

看起来您错过了将外键组 ID 插入表中，从您收到的错误中可以看出，如下所示:

The SQL statement executed was: INSERT INTO tbl_member (firstname, lastname, gender, membersince) VALUES (:yp0, :yp1, :yp2, :yp3)

基本上，您还必须插入 group_id，因此也将 group_id 输入字段添加到成员(member)模型的表单中。

让我知道您是否需要更多帮助。