Reflect unique constraints when reflecting a Table object

Calls to reflect a table did not create any UniqueConstraint objects.
The reflection core made no calls to get_unique_constraints and as
a result, the sqlite dialect would never reflect any unique constraints.

MySQL transparently converts unique constraints into unique indexes, but
SQLAlchemy would reflect those as an Index object and as a
UniqueConstraint. The reflection core will now deduplicate the unique
constraints.

PostgreSQL would reflect unique constraints as an Index object and as
a UniqueConstraint object. The reflection core will now deduplicate
the unique indexes.

4 files changed, 58 insertions, 10 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 7ccd59abb..2f85a3626 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2590,7 +2590,8 @@ class MySQLDialect(default.DefaultDialect):
         return [
             {
                 'name': key['name'],
-                'column_names': [col[0] for col in key['columns']]
+                'column_names': [col[0] for col in key['columns']],
+                'duplicates_index': key['name'],
             }
             for key in parsed_state.keys
             if key['type'] == 'UNIQUE'
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index b9a0d461b..556493b3c 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2471,14 +2471,19 @@ class PGDialect(default.DefaultDialect):
           SELECT
               i.relname as relname,
               ix.indisunique, ix.indexprs, ix.indpred,
-              a.attname, a.attnum, ix.indkey%s
+              a.attname, a.attnum, c.conrelid, ix.indkey%s
           FROM
               pg_class t
                     join pg_index ix on t.oid = ix.indrelid
-                    join pg_class i on i.oid=ix.indexrelid
+                    join pg_class i on i.oid = ix.indexrelid
                     left outer join
                         pg_attribute a
-                        on t.oid=a.attrelid and %s
+                        on t.oid = a.attrelid and %s
+                    left outer join
+                        pg_constraint c
+                        on (ix.indrelid = c.conrelid and
+                            ix.indexrelid = c.conindid and
+                            c.contype in ('p', 'u', 'x'))
           WHERE
               t.relkind IN ('r', 'v', 'f', 'm')
               and t.oid = :table_oid
@@ -2501,7 +2506,7 @@ class PGDialect(default.DefaultDialect):
 
         sv_idx_name = None
         for row in c.fetchall():
-            idx_name, unique, expr, prd, col, col_num, idx_key = row
+            idx_name, unique, expr, prd, col, col_num, conrelid, idx_key = row
 
             if expr:
                 if idx_name != sv_idx_name:
@@ -2523,11 +2528,14 @@ class PGDialect(default.DefaultDialect):
                 index['cols'][col_num] = col
             index['key'] = [int(k.strip()) for k in idx_key.split()]
             index['unique'] = unique
+            index['duplicates_constraint'] = (None if conrelid is None
+                                                   else idx_name)
 
         return [
             {'name': name,
              'unique': idx['unique'],
-             'column_names': [idx['cols'][i] for i in idx['key']]}
+             'column_names': [idx['cols'][i] for i in idx['key']],
+             'duplicates_constraint': idx['duplicates_constraint']}
             for name, idx in indexes.items()
         ]
 
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index c0a3240a5..330fc2b19 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -603,12 +603,15 @@ class Inspector(object):
             columns = index_d['column_names']
             unique = index_d['unique']
             flavor = index_d.get('type', 'index')
+            duplicates = index_d.get('duplicates_constraint')
             if include_columns and \
                     not set(columns).issubset(include_columns):
                 util.warn(
                     "Omitting %s key for (%s), key covers omitted columns." %
                     (flavor, ', '.join(columns)))
                 continue
+            if duplicates:
+                continue
             # look for columns by orig name in cols_by_orig_name,
             # but support columns that are in-Python only as fallback
             idx_cols = []
@@ -626,3 +629,34 @@ class Inspector(object):
                     idx_cols.append(idx_col)
 
             sa_schema.Index(name, *idx_cols, **dict(unique=unique))
+
+        # Unique Constraints
+        constraints = self.get_unique_constraints(table_name, schema)
+        for const_d in constraints:
+            conname = const_d['name']
+            columns = const_d['column_names']
+            duplicates = const_d.get('duplicates_index')
+            if include_columns and \
+                    not set(columns).issubset(include_columns):
+                util.warn(
+                    "Omitting unique constraint key for (%s), "
+                    "key covers omitted columns." %
+                    ', '.join(columns))
+                continue
+            if duplicates:
+                continue
+            # look for columns by orig name in cols_by_orig_name,
+            # but support columns that are in-Python only as fallback
+            constrained_cols = []
+            for c in columns:
+                try:
+                    constrained_col = cols_by_orig_name[c] \
+                        if c in cols_by_orig_name else table.c[c]
+                except KeyError:
+                    util.warn(
+                        "unique constraint key '%s' was not located in "
+                        "columns for table '%s'" % (c, table_name))
+                else:
+                    constrained_cols.append(constrained_col)
+            table.append_constraint(
+                sa_schema.UniqueConstraint(*constrained_cols, name=conname))
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 690a880bb..bd0be5738 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -487,10 +487,12 @@ class ComponentReflectionTest(fixtures.TablesTest):
     @testing.requires.temp_table_reflection
     def test_get_temp_table_unique_constraints(self):
         insp = inspect(self.metadata.bind)
-        eq_(
-            insp.get_unique_constraints('user_tmp'),
-            [{'column_names': ['name'], 'name': 'user_tmp_uq'}]
-        )
+        reflected = insp.get_unique_constraints('user_tmp')
+        for refl in reflected:
+            # Different dialects handle duplicate index and constraints
+            # differently, so ignore this flag
+            refl.pop('duplicates_index', None)
+        eq_(reflected, [{'column_names': ['name'], 'name': 'user_tmp_uq'}])
 
     @testing.requires.temp_table_reflection
     def test_get_temp_table_indexes(self):
@@ -544,6 +546,9 @@ class ComponentReflectionTest(fixtures.TablesTest):
         )
 
         for orig, refl in zip(uniques, reflected):
+            # Different dialects handle duplicate index and constraints
+            # differently, so ignore this flag
+            refl.pop('duplicates_index', None)
             eq_(orig, refl)
 
     @testing.provide_metadata