Database Migrations - adding a primary key

Me again, will probably find the answer shortly after posting again.

Trying to figure out how to create a primary key consisting of two columns, as per the Agile Web app book. I have tried:

$this->createIndex('PRIMARY', 'tbl_project_user_assignment', 'project_id, user_id', 'true');

but that returns:

*** applying m110828_152005_create_project_user_assignment_table

    > create table tbl_project_user_assignment ... done (time: 0.019s)

    > create unique index PRIMARY on tbl_project_user_assignment (project_id, user_id) ...Exception: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name 'PRIMARY'. The SQL statement executed was: CREATE UNIQUE INDEX `PRIMARY` ON `tbl_project_user_assignment` (`project_id`, `user_id`)

...

which is obviously wrong.

If I’m adding the primary key after creating the table it needs to be something like:

ALTER TABLE `trackstar`.`tbl_project_user_assignment` ADD PRIMARY KEY (  `project_id` ,  `user_id` )

which I don’t seem to be able to do. I tried:

public function safeUp()

  {

    $this->createTable('tbl_project_user_assignment', array(

        'project_id' => 'pk',

        'user_id' => 'pk',

        'create_time' => 'datetime',

        'create_user_id' => 'integer',

        'update_time' => 'integer',

        'update_user_id' => 'integer',

    ));

which gave me:

*** applying m110828_152005_create_project_user_assignment_table

    > create table tbl_project_user_assignment ...Exception: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key. The SQL statement executed was: CREATE TABLE `tbl_project_user_assignment` (...

How do I go about creating a Primary key of two columns? Or do I have to use something like:

$this->execute('ALTER TABLE `trackstar`.`tbl_project_user_assignment` ADD PRIMARY KEY ( `project_id`, `user_id` )');

after creating the table, which breaks the DB agnostic approach of using migrations? I suppose you could check to see what DB engine is used in the configuration and write the necessary raw sql for each possibility.

Thanks!

I do this in three different migrations.

First, the initial schema, like this:

    	$this->createTable('{{action_log}}',

            	array(

                	'id' => 'pk',

                	'type' => 'string NOT NULL',

                	'author_id' => 'integer NOT NULL',

                	'theDate' => 'datetime NOT NULL',

                	'url' => 'string NOT NULL',

                	'project_id' => 'integer NOT NULL',

                	'subject' => 'string NOT NULL',

                	'description' => 'text NOT NULL',

            	),"ENGINE=InnoDB DEFAULT CHARSET=utf8");

    	$this->createIndex('type', '{{action_log}}', 'type');

    	$this->createIndex('fk_action_log_user_id', '{{action_log}}', 'author_id');

    	$this->createIndex('fk_action_log_project_id', '{{action_log}}', 'project_id');

Then the data - the second migration script:

        	$sql = "";

        	$fd = fopen(dirname(__FILE__).'/../data/data.sql', "r");

        	if($fd) {

            	while (!feof($fd)) {

                	$sql .= fread($fd, 1024);

            	}

            	fclose($fd);

        	}

        	$this->execute($sql);

Lastly, I add the constraints (foreign keys) in the third migration script:

    	$this->addForeignKey('fk_action_log_project_id',

        	'{{action_log}}',

        	'project_id',

        	'{{project}}', 'id',

        	'CASCADE', 'CASCADE'

    	);

The sql file is a data dump, but where all the table names are replaced with Yii friendly names:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


INSERT INTO `{{auth_assignment}}` (`itemname`, `userid`, `bizrule`, `data`) VALUES

('Admin', '1', 'NULL', 'N'),

('Project Lead', '1', NULL, 'N;'),

('User', '1', NULL, 'N;');

The data is the only thing which is schema specific - but that’s because I am lazy.

And you are right: you don’t create a primary key explicitly - the ‘pk’ type is enough.

‘pk’ assumes int, but what if your primary key is not an int?

    	$this->createTable('{{auth_assignment}}',

            	array(

                	'itemname' => 'string NOT NULL',

                	'userid' => 'string NOT NULL',

                	'bizrule' => 'text',

                	'data' => 'text',

                	'PRIMARY KEY (`itemname`,`userid`)'

            	),"ENGINE=InnoDB DEFAULT CHARSET=utf8");

    	$this->createTable('{{auth_item}}',

            	array(

                	'name' => 'string NOT NULL',

                	'type' => 'integer NOT NULL',

                	'description' => 'text',

                	'bizrule' => 'text',

                	'data' => 'text',

                	'PRIMARY KEY (`name`)'

            	),"ENGINE=InnoDB DEFAULT CHARSET=utf8");

Sweet, thank you very much. The ‘PRIMARY KEY (itemname,userid)’ works. Is that syntax db agnostic or specific to MySQL? (I ask because I will be migrating to PostgreSQL at some point).

I’m using a separate migration for each of the tables required rather than creating them all in one migration as I feel it’s easier for me to keep track of what happens where and when.

Another thanks for making me finally figure out how to make {{table_name}} works in migrations.

Unfortunately it is not DB agnostic. You will need to implement your own customized solution for each DB type.