[dhampik]

I am writing a db migration scripts in my project.

I want to create the following table:

CREATE TABLE `srn_au_regions` (
	`au_id` INT(10) UNSIGNED NOT NULL,
	`region_id` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`au_id`, `region_id`),
	INDEX `FK_srn_au_regions_am` (`au_id`),
	INDEX `FK_srn_au_regions_region` (`region_id`),
	CONSTRAINT `FK_srn_au_regions_am` FOREIGN KEY (`au_id`) REFERENCES `srn_au` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_srn_au_regions_region` FOREIGN KEY (`region_id`) REFERENCES `srn_regions` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

But I'm unable to do it with CDbMigration

I am able to create the table without primary key or with a single primary key

		$this->createTable('{{au_regions}}', array(
			'au_id' => 'int NOT NULL',
			'region_id' => 'int NOT NULL',
		));

But how to create a composite primary key like PRIMARY KEY (`au_id`, `region_id`) using CDbMigration?

If CDbMigration does not allow the whole functionality of SQL then it's worthless!!!

[samdark]

Try this:

$this->createTable('{{au_regions}}', array(
  'au_id' => 'int NOT NULL',
  'region_id' => 'int NOT NULL',
  'PRIMARY KEY (`au_id`, `region_id`)'
));

For foreign keys you can use:

// ALTER TABLE `tbl_profile` ADD CONSTRAINT `fk_profile_user_id`
// FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`)
// ON DELETE CASCADE ON UPDATE CASCADE
$this->addForeignKey('fk_profile_user_id', 'tbl_profile', 'user_id',
    'tbl_user', 'id', 'CASCADE', 'CASCADE')

[dhampik]

samdark, on 01 February 2011 - 12:33 PM, said:

Try this:

$this->createTable('{{au_regions}}', array(
  'au_id' => 'int NOT NULL',
  'region_id' => 'int NOT NULL',
  'PRIMARY KEY (`au_id`, `region_id`)'
));

Wow! This actually works! Have not seen this way in documentation, probably missed it.
Thanks for a really fast reply!

I've been already trying to do like that:

$pkCommand = new CDbCommand($this->dbConnection, 'ALTER TABLE srn_au_regions ADD PRIMARY KEY (au_id, region_id)');
$pkCommand->execute();

But that doesn't seemed to be "the right way"

The way you proposed is really great.

[Luis Lobo Borobia]

@Samdark,

That solution works, but, wouldn't it be "clearer" if composite is handled by the framework?
Composite keys are very frequent in normal use. It's on every relation based table.

I'm thinking on being able to set several columns to pk, and internally, that sould be handled.

What do you think?

[ignition25]

I agree; this should be built into the framework

[Nayjest]

Yes, this uniquely must be built into framework

[Deckard]

Nayjest, on 02 August 2011 - 09:39 AM, said:

Yes, this uniquely must be built into framework

Yes, let alone for cross-compatibility between different DB types.

[ridget]

Will probably submit a pull request for this, but have added functionality for this here

http://www.yiiframew...ration-command/

see gist:

https://gist.github.com/2957501