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

1 files changed, 54 insertions, 0 deletions

diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index f8f93b563..49aafa879 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -1581,6 +1581,59 @@ class IdentityReflectionTest(fixtures.TablesTest):
                 )
 
 
+class CompositeKeyReflectionTest(fixtures.TablesTest):
+    __backend__ = True
+
+    @classmethod
+    def define_tables(cls, metadata):
+        tb1 = Table(
+            "tb1",
+            metadata,
+            Column("id", Integer),
+            Column("attr", Integer),
+            Column("name", sql_types.VARCHAR(20)),
+            sa.PrimaryKeyConstraint("name", "id", "attr", name="pk_tb1"),
+            schema=None,
+            test_needs_fk=True,
+        )
+        Table(
+            "tb2",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("pid", Integer),
+            Column("pattr", Integer),
+            Column("pname", sql_types.VARCHAR(20)),
+            sa.ForeignKeyConstraint(
+                ["pname", "pid", "pattr"],
+                [tb1.c.name, tb1.c.id, tb1.c.attr],
+                name="fk_tb1_name_id_attr",
+            ),
+            schema=None,
+            test_needs_fk=True,
+        )
+
+    @testing.requires.primary_key_constraint_reflection
+    @testing.provide_metadata
+    def test_pk_column_order(self):
+        # test for issue #5661
+        meta = self.metadata
+        insp = inspect(meta.bind)
+        primary_key = insp.get_pk_constraint(self.tables.tb1.name)
+        eq_(primary_key.get("constrained_columns"), ["name", "id", "attr"])
+
+    @testing.requires.foreign_key_constraint_reflection
+    @testing.provide_metadata
+    def test_fk_column_order(self):
+        # test for issue #5661
+        meta = self.metadata
+        insp = inspect(meta.bind)
+        foreign_keys = insp.get_foreign_keys(self.tables.tb2.name)
+        eq_(len(foreign_keys), 1)
+        fkey1 = foreign_keys[0]
+        eq_(fkey1.get("referred_columns"), ["name", "id", "attr"])
+        eq_(fkey1.get("constrained_columns"), ["pname", "pid", "pattr"])
+
+
 __all__ = (
     "ComponentReflectionTest",
     "QuotedNameArgumentTest",
@@ -1589,4 +1642,5 @@ __all__ = (
     "NormalizedNameTest",
     "ComputedReflectionTest",
     "IdentityReflectionTest",
+    "CompositeKeyReflectionTest",
 )