Re: [sqlalchemy] Duplicate constraint name

[Werner]

Hi Michael,

Yes, I am using the old "naming" recipe, adjusted it to check for 
_unnamed_ as a temp fix until I can move to the new way of doing it.

Thanks for the fast answer
Werner

On 1/30/2015 18:27, Michael Bayer wrote:

Werner <werner...@gmx.ch> wrote:

I am getting the following exception on 
"dbCurrent.metadata.create_all(engCurrent)" for the Currency class.

It is due to the "Constraint" for 'home' and 'used' columns having the same 
name.

Is this due to the BOOLEAN type and what is the correct way of uniquely naming 
the Constraint?

Is this a recent change as I don't seem to get this on 0.9.3, below is with 
Python 3.4 and 0.9.7.
the _unnamed_ token is part of the mechanics of the default name given to the 
CheckConstraint within the boolean.  It should never be rendered, as the 
compiler tests for the class of this string and skips if present, and 
additionally it would not be made part of a naming convention (e.g. as 
documented at 
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=naming#configuring-constraint-naming-conventions),
 because the token is a special class that is meant to instruct the naming 
system to skip applying a convention.

I see that you have some naming convention with “ck_currency_<existing_name>” 
coming in, I’m not sure what system you’re using to generate that.  If its the 
naming_convention feature, I need to see the naming_convention setup because that 
would be a bug - the BOOLEAN type with no name given directly will bypass the naming 
convention feature and I can confirm this works.   There seems to already be a bug 
here anyway, but not quite the one you’re seeing.

If the naming convention is something custom (perhaps the old recipe we had), 
that custom thing has to be adjusted to look for this condition.

The BOOLEAN type allows the constraint name to be given a name by passing 
“name=‘<the name>’” to the BOOLEAN constructor.




Werner

class Currency(DeclarativeBase, mix.StandardColumnMixin):
    __tablename__ = u'currency'

    name = sa.Column(sa.Unicode(length=50), nullable=False, index=True)
    code = sa.Column(sa.Unicode(length=3))
    exchangerate = sa.Column(sa.Numeric(precision=15, scale=6))
    home = sa.Column(sa.BOOLEAN(), default=False)
    used = sa.Column(sa.BOOLEAN(), default=False)
    sortorder = sa.Column(sa.Integer())

    __mapper_args__ = {
        'order_by': "home DESC NULLS LAST, used DESC, sortorder DESC NULLS LAST, 
UPPER(name)"}


File "d:\devMine\twcbv5\twcbsrc\controllers\app_cb.py", line 1271, in <module>
  app = ab.BaseApp(redirect=True, filename=appLog)
File "c:\Python34\Lib\site-packages\wx\core.py", line 1864, in __init__
  self._BootstrapApp()
File "d:\devMine\twcbv5\twcbsrc\app_base.py", line 169, in OnInit
  self.updateDB()
File "d:\devMine\twcbv5\twcbsrc\app_base.py", line 502, in updateDB
  if not dbupg.dbUpgrade(prefMinimal):
File "d:\devMine\twcbv5\twcbsrc\dbupdatetools\dbupgrade.py", line 59, in 
dbUpgrade
  if not dbupgv50.doUpgrade(progdlg):
File "d:\devMine\twcbv5\twcbsrc\dbupdatetools\dbupgradev50.py", line 382, in 
doUpgrade
  dbCurrent.metadata.create_all(engCurrent)
File "c:\Python34\Lib\site-packages\sqlalchemy\sql\schema.py", line 3352, in 
create_all
  tables=tables)
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 1617, in 
_run_visitor
  conn._run_visitor(visitorcallable, element, **kwargs)
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 1246, in 
_run_visitor
  **kwargs).traverse_single(element)
File "c:\Python34\Lib\site-packages\sqlalchemy\sql\visitors.py", line 120, in 
traverse_single
  return meth(obj, **kw)
File "c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py", line 713, in 
visit_metadata
  self.traverse_single(table, create_ok=True)
File "c:\Python34\Lib\site-packages\sqlalchemy\sql\visitors.py", line 120, in 
traverse_single
  return meth(obj, **kw)
File "c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py", line 732, in 
visit_table
  self.connection.execute(CreateTable(table))
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 729, in 
execute
  return meth(self, multiparams, params)
File "c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py", line 69, in 
_execute_on_connection
  return connection._execute_ddl(self, multiparams, params)
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 783, in 
_execute_ddl
  compiled
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 958, in 
_execute_context
  context)
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 1160, in 
_handle_dbapi_exception
  exc_info
File "c:\Python34\Lib\site-packages\sqlalchemy\util\compat.py", line 188, in 
raise_from_cause
  reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "c:\Python34\Lib\site-packages\sqlalchemy\util\compat.py", line 181, in 
reraise
  raise value.with_traceback(tb)
File "c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py", line 951, in 
_execute_context
  context)
File 
"c:\Python34\Lib\site-packages\sqlalchemy\dialects\firebird\kinterbasdb.py", 
line 110, in do_execute
  cursor.execute(statement, parameters or [])
File "c:\Python34\Lib\site-packages\fdb\fbcore.py", line 3353, in execute
  self._ps._execute(parameters)
File "c:\Python34\Lib\site-packages\fdb\fbcore.py", line 3080, in _execute
  "Error while executing SQL statement:")

sqlalchemy.exc.DatabaseError: (DatabaseError) ('Error while executing SQL statement:\n- 
SQLCODE: -607\n- unsuccessful metadata update\n- STORE RDB$RELATION_CONSTRAINTS failed\n- 
attempt to store duplicate value (visible to active transactions) in unique index 
"RDB$INDEX_12"', -607, 335544351) '\nCREATE TABLE currency (\n\tid KEYS NOT 
NULL, \n\tname VARCHAR(50) NOT NULL, \n\tcode VARCHAR(3), \n\texchangerate NUMERIC(15, 
6), \n\thome BOOLEAN, \n\tused BOOLEAN, \n\tsortorder INTEGER, \n\tcreated_at TIMESTAMP, 
\n\tupdated_at TIMESTAMP, \n\tcreated_by KEYS, \n\tupdated_by KEYS, \n\tCONSTRAINT 
pk_currency PRIMARY KEY (id), \n\tCONSTRAINT ck_currency__unnamed_ CHECK (home IN (0, 
1)), \n\tCONSTRAINT ck_currency__unnamed_ CHECK (used IN (0, 1))\n)\n\n' ()

--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.