Theocharis K. Theocharis K. - 1 year ago 158
MySQL Question

How to alter composite primary key to add additional column

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 Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download