1.. _tutorial_constraint_names: 2 3The Importance of Naming Constraints 4==================================== 5 6An important topic worth mentioning is that of constraint naming conventions. 7As we've proceeded here, we've talked about adding tables and columns, and 8we've also hinted at lots of other operations listed in :ref:`ops` such as those 9which support adding or dropping constraints like foreign keys and unique 10constraints. The way these constraints are referred to in migration scripts 11is by name, however these names by default are in most cases generated by 12the relational database in use, when the constraint is created. For example, 13if you emitted two CREATE TABLE statements like this on Postgresql:: 14 15 test=> CREATE TABLE user_account (id INTEGER PRIMARY KEY); 16 CREATE TABLE 17 test=> CREATE TABLE user_order ( 18 test(> id INTEGER PRIMARY KEY, 19 test(> user_account_id INTEGER REFERENCES user_account(id)); 20 CREATE TABLE 21 22Suppose we wanted to DROP the REFERENCES that we just applied to the 23``user_order.user_account_id`` column, how do we do that? At the prompt, 24we'd use ``ALTER TABLE <tablename> DROP CONSTRAINT <constraint_name>``, or if 25using Alembic we'd be using :meth:`.Operations.drop_constraint`. But both 26of those functions need a name - what's the name of this constraint? 27 28It does have a name, which in this case we can figure out by looking at the 29Postgresql catalog tables:: 30 31 test=> SELECT r.conname FROM 32 test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 33 test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid 34 test-> WHERE c.relname='user_order' AND r.contype = 'f' 35 test-> ; 36 conname 37 --------------------------------- 38 user_order_user_account_id_fkey 39 (1 row) 40 41The name above is not something that Alembic or SQLAlchemy created; 42``user_order_user_account_id_fkey`` is a naming scheme used internally by 43Postgresql to name constraints that are otherwise not named. 44 45This scheme doesn't seem so complicated, and we might want to just use our 46knowledge of it so that we know what name to use for our 47:meth:`.Operations.drop_constraint` call. But is that a good idea? What 48if for example we needed our code to run on Oracle as well. OK, certainly 49Oracle uses this same scheme, right? Or if not, something similar. Let's 50check:: 51 52 Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production 53 54 SQL> CREATE TABLE user_account (id INTEGER PRIMARY KEY); 55 56 Table created. 57 58 SQL> CREATE TABLE user_order ( 59 2 id INTEGER PRIMARY KEY, 60 3 user_account_id INTEGER REFERENCES user_account(id)); 61 62 Table created. 63 64 SQL> SELECT constraint_name FROM all_constraints WHERE 65 2 table_name='USER_ORDER' AND constraint_type in ('R'); 66 67 CONSTRAINT_NAME 68 ----------------------------------------------------- 69 SYS_C0029334 70 71Oh, we can see that is.....much worse. Oracle's names are entirely unpredictable 72alphanumeric codes, and this will make being able to write migrations 73quite tedious, as we'd need to look up all these names. 74 75The solution to having to look up names is to make your own names. This is 76an easy, though tedious thing to do manually. For example, to create our model 77in SQLAlchemy ensuring we use names for foreign key constraints would look like:: 78 79 from sqlalchemy import MetaData, Table, Column, Integer, ForeignKey 80 81 meta = MetaData() 82 83 user_account = Table('user_account', meta, 84 Column('id', Integer, primary_key=True) 85 ) 86 87 user_order = Table('user_order', meta, 88 Column('id', Integer, primary_key=True), 89 Column('user_order_id', Integer, 90 ForeignKey('user_account.id', name='fk_user_order_id')) 91 ) 92 93Simple enough, though this has some disadvantages. The first is that it's tedious; 94we need to remember to use a name for every :class:`~sqlalchemy.schema.ForeignKey` object, 95not to mention every :class:`~sqlalchemy.schema.UniqueConstraint`, :class:`~sqlalchemy.schema.CheckConstraint`, 96:class:`~sqlalchemy.schema.Index`, and maybe even :class:`~sqlalchemy.schema.PrimaryKeyConstraint` 97as well if we wish to be able to alter those too, and beyond all that, all the 98names have to be globally unique. Even with all that effort, if we have a naming scheme in mind, 99it's easy to get it wrong when doing it manually each time. 100 101What's worse is that manually naming constraints (and indexes) gets even more 102tedious in that we can no longer use convenience features such as the ``.unique=True`` 103or ``.index=True`` flag on :class:`~sqlalchemy.schema.Column`:: 104 105 user_account = Table('user_account', meta, 106 Column('id', Integer, primary_key=True), 107 Column('name', String(50), unique=True) 108 ) 109 110Above, the ``unique=True`` flag creates a :class:`~sqlalchemy.schema.UniqueConstraint`, but again, 111it's not named. If we want to name it, manually we have to forego the usage 112of ``unique=True`` and type out the whole constraint:: 113 114 user_account = Table('user_account', meta, 115 Column('id', Integer, primary_key=True), 116 Column('name', String(50)), 117 UniqueConstraint('name', name='uq_user_account_name') 118 ) 119 120There's a solution to all this naming work, which is to use an **automated 121naming convention**. For some years, SQLAlchemy has encourgaged the use of 122DDL Events in order to create naming schemes. The :meth:`~sqlalchemy.events.DDLEvents.after_parent_attach` 123event in particular is the best place to intercept when :class:`~sqlalchemy.schema.Constraint` 124and :class:`~sqlalchemy.schema.Index` objects are being associated with a parent 125:class:`~sqlalchemy.schema.Table` object, and to assign a ``.name`` to the constraint while making 126use of the name of the table and associated columns. 127 128But there is also a better way to go, which is to make use of a feature 129new in SQLAlchemy 0.9.2 which makes use of the events behind the scenes known as 130:paramref:`~sqlalchemy.schema.MetaData.naming_convention`. Here, we can 131create a new :class:`~sqlalchemy.schema.MetaData` object while passing a dictionary referring 132to a naming scheme:: 133 134 convention = { 135 "ix": "ix_%(column_0_label)s", 136 "uq": "uq_%(table_name)s_%(column_0_name)s", 137 "ck": "ck_%(table_name)s_%(constraint_name)s", 138 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", 139 "pk": "pk_%(table_name)s" 140 } 141 142 metadata = MetaData(naming_convention=convention) 143 144If we define our models using a :class:`~sqlalchemy.schema.MetaData` as above, the given 145naming convention dictionary will be used to provide names for all constraints 146and indexes. 147 148.. _autogen_naming_conventions: 149 150Integration of Naming Conventions into Operations, Autogenerate 151--------------------------------------------------------------- 152 153As of Alembic 0.6.4, the naming convention feature is integrated into the 154:class:`.Operations` object, so that the convention takes effect for any 155constraint that is otherwise unnamed. The naming convention is passed to 156:class:`.Operations` using the :paramref:`.MigrationsContext.configure.target_metadata` 157parameter in ``env.py``, which is normally configured when autogenerate is 158used:: 159 160 # in your application's model: 161 162 meta = MetaData(naming_convention={ 163 "ix": "ix_%(column_0_label)s", 164 "uq": "uq_%(table_name)s_%(column_0_name)s", 165 "ck": "ck_%(table_name)s_%(constraint_name)s", 166 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", 167 "pk": "pk_%(table_name)s" 168 }) 169 Base = declarative_base(metadata=meta) 170 171 # .. in your Alembic env.py: 172 173 # add your model's MetaData object here 174 # for 'autogenerate' support 175 from myapp import mymodel 176 target_metadata = mymodel.Base.metadata 177 178 # ... 179 180 def run_migrations_online(): 181 182 # ... 183 184 context.configure( 185 connection=connection, 186 target_metadata=target_metadata 187 ) 188 189Above, when we render a directive like the following:: 190 191 op.add_column('sometable', Column('q', Boolean(name='q_bool'))) 192 193The Boolean type will render a CHECK constraint with the name 194``"ck_sometable_q_bool"``, assuming the backend in use does not support 195native boolean types. 196 197We can also use op directives with constraints and not give them a name 198at all, if the naming convention doesn't require one. The value of 199``None`` will be converted into a name that follows the appropriate naming 200conventions:: 201 202 def upgrade(): 203 op.create_unique_constraint(None, 'some_table', 'x') 204 205When autogenerate renders constraints in a migration script, it renders them 206typically with their completed name. If using at least Alembic 0.6.4 as well 207as SQLAlchemy 0.9.4, these will be rendered with a special directive 208:meth:`.Operations.f` which denotes that the string has already been 209tokenized:: 210 211 def upgrade(): 212 op.create_unique_constraint(op.f('uq_const_x'), 'some_table', 'x') 213 214 215For more detail on the naming convention feature, see :ref:`sqla:constraint_naming_conventions`. 216