How to alter composite primary key to add additional column

Question

Let's support I have a migration, let's call it 001_Create_organization_users.php with two columns, X,Y that they are both a composed PK:

<?php

namespace Fuel\Migrations;

class Create_organizations_users
{
    public function up()
    {
        \DBUtil::create_table('organizations_users', array(
            'X' => array('constraint' => 11, 'type' => 'int'),
            'Y' => array('constraint' => 11, 'type' => 'int')
        ), array('X', 'Y')); // Primary Key
    }
// Other functions
}

Then I add a column to that table with a different migration, let's call it 002_Add_column_to_organization_users.php

<?php

namespace Fuel\Migrations;

class Add_column_to_organization_users.php
{
    public function up()
    {
        $fields = array(
            'Z' => array(
                'constraint' => 11, 'type' => 'int'),
        );

 \DBUtil::add_fields('organizations_users', $fields);
 //....
}

Can I somehow edit that migration so I can add the column 'Z' as a primary key with the previously entered keys so that my final primary key is X,Y,Z ?

Answer 1

Since, you have already created PRIMARY KEY, then you need to drop it first and then create new one with new set of columns.

ALTER TABLE <table_name> DROP PRIMARY KEY, ADD PRIMARY KEY(X, Y, Z);