Yii Framework Forum: Unable to create composite primary key with CDbMigration - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Unable to create composite primary key with CDbMigration Rate Topic: -----

#1 User is offline   dhampik 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 25-January 11
  • Location:Russia

Posted 01 February 2011 - 11:57 AM

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!!!
0

#2 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,376
  • Joined: 17-January 09
  • Location:Russia

Posted 01 February 2011 - 12:33 PM

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')

Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
4

#3 User is offline   dhampik 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 25-January 11
  • Location:Russia

Posted 01 February 2011 - 12:48 PM

View Postsamdark, 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.
0

#4 User is offline   Luis Lobo Borobia 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 34
  • Joined: 25-March 10
  • Location:Buenos Aires

Posted 17 July 2011 - 10:02 PM

@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?
Posted Image
The Social Network for Artists
0

#5 User is offline   ignition25 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 24
  • Joined: 25-November 10

Posted 18 July 2011 - 11:31 AM

I agree; this should be built into the framework
0

#6 User is offline   Nayjest 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 85
  • Joined: 05-June 10
  • Location:Ukraine, Kiev

Posted 02 August 2011 - 09:39 AM

Yes, this uniquely must be built into framework
Web-superman. My blog about web development (russian).
0

#7 User is offline   Deckard 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 21-October 11

Posted 30 May 2012 - 09:30 AM

View PostNayjest, 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.
0

#8 User is offline   ridget 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 28-May 12

Posted 19 June 2012 - 09:10 PM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users