set primay key

Archives Yii 1.0.x Discussions
1

hi all,

I don’t want an auto increment key, How can I set the primary key of a table manually?

in my controller I write:




$va = new va;

$va->primaryKey("id", $_POST["va_id"]);

$va->setAttribute("vqIq", $_POST["va_vqId"]);

$va->save(false,array('id','vqId'));

but i get this error:

[color="#FF0000"]CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘id’ cannot be null[/color]

this is the table structure:

[sql]

CREATE TABLE IF NOT EXISTS va (

id varchar(16) NOT NULL,

ytId varchar(11) DEFAULT NULL,

vqId varchar(16) NOT NULL,

PRIMARY KEY (id),

KEY vqId (vqId)

)ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE va ADD CONSTRAINT va_ibfk_1 FOREIGN KEY (vqId) REFERENCES vq (id)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

[/sql]

2

Example for use "composed key" how primary key

IN MODEL:


    public function primaryKey() {

        return array('id_prova'=>$this->id_prova,'id_turno'=>$this->id_turno,'data'=>$this->data,'ano_letivo'=>$this->ano_letivo);

    }

IN CONTROLLER:

$model->primaryKey();

3

Thanks Armando but I made a mistake


$va->primaryKey("id", $_POST["va_id"]);

is a method that does not exist in yii framework. The error reported in my prevous post is caused by


$va->setAttribute("id", $_POST["va_id"]);

Table va has a simple (not composite) primary key called id without AUTO INCREMENT.

I’m looking for something like


setPrymaryKey()

because the method


$va->setAttribute("id", $_POST["va_id"]);

does’nt insert any value in the table.

Is it possible in yii to have an CActiveRecord without auto increment prymary key?

4

yeah! it´s possible, just set the primary key for any field. (not to need to be auto increment key)

5

I guess i have a similar problem.

My problem is that I have a "editable" primary key of type varchar(9). I have added the key column to the form and the safeAttributes in the model. But when i call save() and see the query done in the database i see something like following:




UPDATE `tbproject` SET `ID`='mynewkey', ... other columns... WHERE `tbproject`.`ID`='mynewkey'

where ‘mynewkey’ is the value entered on the form. The problem is that due to the form post I overwrite the “old” value of my key which is in the database.

And therefore it doesn’t update anything cause the wrong key is used in the where part.

the correct sql would be




UPDATE `tbproject` SET `ID`='mynewkey', ... other columns... WHERE `tbproject`.`ID`='myoldkey'

Does someone know how to solve this or show me the right way to change the primary key value of a database entry?

6

Wow, reading all this, it seems to be a good idea to use auto incremented integers as id.

I was just about to modify the CRUD templates, because I realized I wasn’t able to insert new objects that used a varchar as id. Now that I see that there are so much other related problems, I think I will modify my db schema. :(

7

Hi All,

[member=‘Armando’] thanks for your help.

At the end I discovered the problem I had was not due to yii framework but in the way I accessed the $_POST[] variable, I was trying to insert a NULL value as a primary key!!!

I put the code listed below in my controller and everything works perfectly :D




   $va = new va;

   $va->attributes=$_POST['va'];//CORRECT WAY

 //$va->setAttribute("id", $_POST["va_id"]);//WRONG WAY

 //$va->setAttribute("vqIq", $_POST["va_vqId"]);//WRONG WAY

   $va->save();