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