Changing primary key on MySQL #481

Closed
Meijuh opened this Issue Oct 5, 2012 · 5 comments

Projects

None yet

4 participants

@Meijuh
Meijuh commented Oct 5, 2012

The propel-gen diff task generates wrong SQL for MySQL when a primary key changes. For example if we have a table (id, col_a, col_b) and the primary key is currently on id and the new primary key is on (col_a, col_b) then the generated SQL will be:

ALTER TABLE table DROP PRIMARY KEY;
ALTER TABLE `table ADD PRIMARY KEY (col_a, col_b);

MySQL won't accept this, because there must always be a primary key I guess.

The SQL that does work is

ALTER TABLE table DROP PRIMARY KEY, ADD PRIMARY KEY (col_a, col_b);

@rozwell
rozwell commented Oct 5, 2012

Primary key isn't required to create table in MySQL and 2 queries will work just fine.
It didn't work for you? On what MySQL version? What query exactly?

Please don't guess and double check before posting an issue.

Just tested and no error whatsoever:

DROP TABLE IF EXISTS pk_test;
CREATE TABLE pk_test
(
    col1 INTEGER,
    col2 INTEGER,
    PRIMARY KEY (col1)
) ENGINE=InnoDB;
ALTER TABLE pk_test DROP PRIMARY KEY;
ALTER TABLE pk_test ADD PRIMARY KEY (col1,col2);
DROP TABLE pk_test;

-- # MySQL returned an empty result set (i.e. zero rows).
@Meijuh
Meijuh commented Oct 5, 2012

Sorry, it appears I have over simplified my example a bit. It goes wrong if there also exists a column with auto_increment.

Suppose the original primary key was on a column with auto_increment.

DROP TABLE IF EXISTS pk_test;
CREATE TABLE pk_test
(
    col0 INTEGER AUTO_INCREMENT,
    col1 INTEGER,
    col2 INTEGER,
    PRIMARY KEY (col0)
) ENGINE=InnoDB;
ALTER TABLE pk_test DROP PRIMARY KEY;
ALTER TABLE pk_test DROP `col0`;
ALTER TABLE pk_test ADD PRIMARY KEY (col1,col2);
DROP TABLE pk_test;

-- # 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key 

So the generated SQL will be the three ALTER TABLE queries instead of a single ALTER TABLE query.

@jaugustin
Member

this is already reported #346

@Meijuh
Meijuh commented Oct 5, 2012

Okey, well I was searching for the exact error message and could not find it.

@willdurand
Member

Closing this issue as it's a duplicate of #346.

@willdurand willdurand closed this Nov 22, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment