From 7937a409f9e685341b6a5b8385491befe0aaa4d4 Mon Sep 17 00:00:00 2001
From: fulpm <8397318+fulpm@users.noreply.github.com>
Date: Tue, 20 Oct 2020 17:54:50 -0400
Subject: Correct reflection for composite primary keys

Fixes: #5661

### Description
Fixes reflection of composite primary keys to maintain the correct column order in the MSSQL
and SQLite dialects.

Closes: #5662
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5662
Pull-request-sha: b568dec7070b4f3ee46a528bdf16fb237baade2a

Change-Id: I452b23cbf7f389c4a0a34cffce5c32498efe37d2
---
 lib/sqlalchemy/dialects/mssql/base.py  | 22 +++++++++++++---------
 lib/sqlalchemy/dialects/sqlite/base.py |  1 +
 2 files changed, 14 insertions(+), 9 deletions(-)

(limited to 'lib/sqlalchemy/dialects')

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index c8f2b4ca3..a224c00bb 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -3168,15 +3168,19 @@ class MSDialect(default.DefaultDialect):
         C = ischema.key_constraints.alias("C")
 
         # Primary key constraints
-        s = sql.select(
-            C.c.column_name, TC.c.constraint_type, C.c.constraint_name
-        ).where(
-            sql.and_(
-                TC.c.constraint_name == C.c.constraint_name,
-                TC.c.table_schema == C.c.table_schema,
-                C.c.table_name == tablename,
-                C.c.table_schema == owner,
-            ),
+        s = (
+            sql.select(
+                C.c.column_name, TC.c.constraint_type, C.c.constraint_name
+            )
+            .where(
+                sql.and_(
+                    TC.c.constraint_name == C.c.constraint_name,
+                    TC.c.table_schema == C.c.table_schema,
+                    C.c.table_name == tablename,
+                    C.c.table_schema == owner,
+                ),
+            )
+            .order_by(TC.c.constraint_name, C.c.ordinal_position)
         )
         c = connection.execution_options(future_result=True).execute(s)
         constraint_name = None
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 8ef35514a..8a4fbe8e5 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1853,6 +1853,7 @@ class SQLiteDialect(default.DefaultDialect):
             constraint_name = result.group(1) if result else None
 
         cols = self.get_columns(connection, table_name, schema, **kw)
+        cols.sort(key=lambda col: col.get("primary_key"))
         pkeys = []
         for col in cols:
             if col["primary_key"]:
-- 
cgit v1.2.1