Issues

Issue #208 resolved

Creating Unique Constraint including Foreign Key Causes autogenerate issues in MySQL

Jonathan Herriott
created an issue

Table Before Migration:

CREATE TABLE my_table ( id bigint(20) NOT NULL AUTO_INCREMENT, other_table_id bigint(20) NOT NULL, type int(11) NOT NULL, value decimal(40,15) NOT NULL, timestamp datetime NOT NULL, PRIMARY KEY (id), KEY fk_my_table_other_table (other_table_id), CONSTRAINT fk_my_table_other_table_id FOREIGN KEY (other_table_id) REFERENCES other_table (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Migration: op.create_unique_constraint('uc_my_constraint', 'my_table', [ 'other_table_id', 'type', 'timestamp' ])

autogenerate tries to also add in a delete for fk_my_table_other_table KEY which fails because the above unique constraint removes it implicitly

op.drop_index('fk_my_table_other_table_id', table_name='my_table')

Table After Migration: CREATE TABLE my_table ( id bigint(20) NOT NULL AUTO_INCREMENT, other_table_id bigint(20) NOT NULL, type int(11) NOT NULL, value decimal(40,15) NOT NULL, timestamp datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY uc_my_constraint (other_table_id,type,timestamp), CONSTRAINT fk_my_table_other_table_id FOREIGN KEY (other_table_id) REFERENCES other_table (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Any time after this running revision with autogenerate will add

op.drop_index('fk_my_table_other_table_id', table_name='my_table')

Comments (5)

  1. Mike Bayer repo owner

    this part:

    KEY fk_my_table_other_table (other_table_id),

    did you mean

    KEY fk_my_table_other_table_id (other_table_id), ?

    assuming that's what you mean.

  2. Mike Bayer repo owner
    • Liberalized even more the check for MySQL indexes that shouldn't be counted in autogenerate as "drops"; this time it's been reported that an implicitly created index might be named the same as a composite foreign key constraint, and not the actual columns, so we now skip those when detected as well. fixes #208

    → <<cset 1f4d8c610336>>

  3. Log in to comment