Auto-increment

Yii 1.1 General Discussion
1

I need to set a field as auto-increment … how do I?

Otherwise every time I enter I’m going to say

unique constraint violated…

2

I don’t really understand you question…

the field is set as autoincrement in the database… not in the program…

3

Oracle doesn’t have keyword autoincrement…

4

But you can use a sequence and a trigger in oracle to get an autoincrement column. Take a look at this: http://webxadmin.free.fr/article/oracle-autoincrement-columns-134.php

5

Yes but when a create model of my table db…how can I import trigger????

6

As I wrote before… it’s not something that you use in PHP or Yii at all… you just inseart a record without giving a value for the autoincrement field… the database should insert a new value for that field…

it’s database specific… you create a sequence/trigger for the table attribute to fire before insert… something like




CREATE OR REPLACE TRIGGER %triggername%

BEFORE INSERT ON %tablename% FOR EACH ROW

BEGIN

   SELECT %seqname%.NEXTVAL

   INTO :NEW.%columnname%

   FROM DUAL;

END;

Try to search with google for how to implement this in oracle…

7

Hopefully this thread isn’t too old to post to…

I’m using oracle triggers to simulate auto-increment (ID) columns in my tables as described in this thread. When I save a new record (e.g. $subscriber->save()) it successfully creates a new row in the SUBSCRIBER table complete with an auto-incremented ID. Woot! However, back in Yii the model instance doesn’t update with the new ID number. It remains NULL. So when I go to use that ID to fill out a different table record (e.g. $account->SUBSCRIBER_ID = $subscriber->ID;) it fills out the other table record with NULL for that particular column.

Is there some sort of refresh command I have to use to cause the model to update with the auto-incremented ID number? I think the mySQL backend automatically does this on a save().

FYI: I’m using the pdo_oci driver.

Thanks!

8

I have a work around (solution?). I’ve done away with the triggers. I’m now using a CDbCommand to get the next ID in the sequence and manually assigning it to the ID field before I save.

Say you have a table named SUBSCRIBER, an oracle sequencer named SUBSCRIBER_SEQ, and the field you want to auto increment is SUBSCRIBER_ID…

$subscriber = new Subscriber; // Subscriber is a model class based on CActiveRecord

$connection = Yii::app()->db;

$sql = "select SUBSCRIBER_SEQ.NEXTVAL from DUAL";

$command = $connection->createCommand($sql);

$subscriber->SUBSCRIBER_ID = $command->queryScalar();

// fill in other Subscriber fields

$subscriber->save();

This new record’s “auto” incremented id is now in $subscriber->SUBSCRIBER_ID and can be used elsewhere in the code.

Our database naming conventions allowed me to write a new base class for our models that is a very simple extension of the CActiveRecord class. It performs the above functionality in the background using CActiveRecord’s “onBeforeSave” event (http://www.yiiframework.com/forum/index.php?/topic/4533-onbeforesave-problem/). Now my application code looks and behaves as if it were accessing a mySQL database with ID columns that auto-increment.

9

hi

i have a problem that autoincrement value always increase on data incert.

but on delete data autoincrement value not start from least available value.

is there any way to when we delete data autoincrement value automatically use least available value.

please help

thanks in advance.