rename constraint proposal

Issue #348 new
Ofir Herzas
created an issue

I would very much like to see a rename-constraint option.

I have read your comment on #246 (relational databases don't generally have RENAME CONSTRAINT functions) and I'm not sure that that is the case: Oracle and Postgre support it Mssql and Sybase have a stored procedure sp_rename for renaming constraints. * Mysql, Firebird and Sqlite don't currently support it

Meaning that more than half of the dialects supported by sqlalchemy support a rename constraint method while the others can do a drop-create in order to support it.

Supporting the rename constraint should improve performance and readability

Comments (12)

  1. Ofir Herzas reporter

    If some DB's raise a NotImplementedError then it misses the whole point as the developer would have to code according to the dialect (see my comment on #346).

    Nevertheless, I do like the "openness" of the system...

  2. Michael Bayer repo owner

    If some DB's raise a NotImplementedError then it misses the whole point as the developer would have to code according to the dialect

    well then we need to not have a rename_constraint() operation at all as this operation is not possible on those backends. There's some irony that you in fact are asking for a feature that can only be supported on some backends and not others.

    Consider if SQLAlchemy took the approach of "only features that work everywhere". What happens when Postgresql adds new JSON types, window functions, CTEs, all kinds of stuff that everyone wants? We're basically forced to never support them, or alternatively re-implement every feature they ever add on every database backend. Which means we are taking the work of the Postgresql developer team and multiplying it by about a hundred, with an end product that is impossibly byzantine and complicated (if you want to tell me how to replicate a window function in MySQL the world is all ears).

    How is that a viable plan ?

  3. Ofir Herzas reporter

    As I see it, there is a difference between advanced and complex features like windowing and simple features like rename constraint. This is not a black and white situation and each case should be reconsidered.

    In the situation of rename constraint you advise to drop and recreate the constraint and I think that it could be done by SA if the feature is not already implemented in the DB. The other alternative, if the user doesn't want to compromise performance is to differentiate between dialects or to write wrappers and IMO, this is not optimal.

    If an operation is not possible on all backends then either you don't add it or you use the NotImplementedError , but that's not the case for rename as it is possible but with a different "syntax".

    Future versions will also gain from it. Consider that Mysql introduces a rename constraint - all you'd have to do is change the implementation while all users won't have to change anything...

  4. Michael Bayer repo owner

    In the situation of rename constraint you advise to drop and recreate the constraint and I think that it could be done by SA if the feature is not already implemented in the DB.

    it can't. Alembic operations can't do SELECTs because they must work in offline mode. Plus, recreating constraints like CHECK constraints exactly is extremely difficult if not impossible on backends that don't expose the original SQL expression.

    Consider that Mysql introduces a rename constraint

    that would be great, we take off the NotImplementedError() and we're done for that backend :).

  5. Michael Bayer repo owner

    also what would you propose for "CREATE SEQUENCE" ? would a user really want the Sequence() to generate a sequence table + some kind of stored procedure on MySQL ?

  6. Ofir Herzas reporter

    AFAIK, there are no sequences in Mysql so I don't understand how would a user get to rename something that doesn't exist (auto_increment doesn't create a constraint, does it?)

  7. Michael Bayer repo owner

    not talking about rename. Talking about connection.execute(CreateSequence(Sequence('some_seq')). If features that don't work on all databases are no longer allowed what would you do with createsequence?

  8. Ofir Herzas reporter

    I didn't say features that don't work on all databases should no longer be allowed. What I did say is that in those scenarios you can either not implement them on some DBs (like create-sequence) or at all (like windowing). This is the same as now since my focus was on features that can work on all DBs, like rename constraint that can be satisfied by drop and create constraint.

    As to the "how" part, maybe it's impossible, but I think it's irrelevant unless you decide to go for it.

  9. Michael Bayer repo owner

    anyway do you want to add rename_constraint() w/ NotImplemented or not? As always, you can just use the operation extension APi if you just need to solve this problem locally in whatever way you choose (including that the operation does a SELECT for MySQL etc.).

  10. Log in to comment