Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Creating Unique Constraint including Foreign Key Causes autogenerate issues in MySQL #208

Closed
sqlalchemy-bot opened this issue Jun 5, 2014 · 6 comments
Labels
bug

Comments

@sqlalchemy-bot
Copy link

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Migrated issue, originally created by Jonathan Herriott (@herriojr)

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')

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Michael Bayer (@zzzeek) wrote:

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.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Michael Bayer (@zzzeek) wrote:

  • 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

1f4d8c6

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Michael Bayer (@zzzeek) wrote:

please put a little more effort into code formatting (use the bitbucket <> button) and accuracy on the next issue, thanks!

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Jonathan Herriott (@herriojr) wrote:

You were correct on your first assumption.

And sorry about the formatting, it won't happen in the future.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 5, 2014

Michael Bayer (@zzzeek) wrote:

thanks! hope the fix works for you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
You can’t perform that action at this time.