how can i add auto_increment column to work with mysql and postgres databases?

[0xff00ff]

I want to create migration for mysql and postgres databases, but i need an autoincrement column. For mysql it`s simple, auto_increment option works well. but how about postgres?

[ruckus]

The Postgres analog to MySQL auto_increment is serial. So the actual SQL would be:

CREATE TABLE animals (id serial, name text)

To achieve this:

$t = $this->create_table('animals');
// will automatically give you an `id` column of type `serial`

If you wanted a different name than id

// specify id=false so the default primary key is ignored
$table = $bm->create_table('animals', array('id' => false));
$table->column('user_id', 'integer', array('primary_key' => true, 'auto_increment' => true));
$sql = $table->finish();

[0xff00ff]

@ruckus thanks for answer, but just option 'primary_key' is not works. but if i set 'primary_key' as column type (not integer) it works perfect.

[jaffarhussain1011]

@ruckus i am able to create primary key with different column name but I want to specify my own keyname/constraint name so that sql for that should be like below
CONSTRAINT my_custom_key_anme PRIMARY KEY (user_id)
Is there any option to provide that in following query:
$table->column('user_id', 'integer', array('primary_key' => true, 'auto_increment' => true));

Thanks

[ruckus]

Hi @jaffarhussain1011 - you cannot specify a constraint in the same column() method call. You'll need to finish creating the table and then run ad-hoc SQL to add that constraint, like:

// ... column calls
$table->finish();
$this->execute("ALTER TABLE foobar ADD CONSTRAINT ...");

[jaffarhussain1011]

Ok, Thanks for prompt reply.