<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">very simple and the slightest bit not backwards compatible....very unfortunate I wasn't able to see this four years ago, unless I'm missing something.</p>
<p dir="auto">Might have to push this to 0.8.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 lib/sqlalchemy/orm/properties.py
--- a/lib/sqlalchemy/orm/properties.py	Thu Feb 16 19:29:00 2012 -0500
+++ b/lib/sqlalchemy/orm/properties.py	Sat Feb 18 08:39:33 2012 -0500
@@ -1030,7 +1030,8 @@
             # general mapped table, which in the case of inheritance is
             # a join.
             return join_condition(mapper.mapped_table, table, 
-                                        a_subset=mapper.local_table)
+                                        a_subset=mapper.local_table,
+                                        consider_as_foreign_keys=self._user_defined_foreign_keys)
 
         try:
             if self.secondary is not None:
diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 lib/sqlalchemy/sql/util.py
--- a/lib/sqlalchemy/sql/util.py	Thu Feb 16 19:29:00 2012 -0500
+++ b/lib/sqlalchemy/sql/util.py	Sat Feb 18 08:39:33 2012 -0500
@@ -225,7 +225,10 @@
 
     return visitors.cloned_traverse(crit, {}, {'binary':visit_binary})
 
-def join_condition(a, b, ignore_nonexistent_tables=False, a_subset=None):
+def join_condition(a, b, 
+                    ignore_nonexistent_tables=False, 
+                    a_subset=None,
+                    consider_as_foreign_keys=None):
     &quot;&quot;&quot;create a join condition between two tables or selectables.
 
     e.g.::
@@ -269,6 +272,9 @@
                 else:
                     continue
 
+            if consider_as_foreign_keys and \
+                fk.parent not in consider_as_foreign_keys:
+                continue
             if col is not None:
                 crit.append(col == fk.parent)
                 constraints.add(fk.constraint)
@@ -286,6 +292,9 @@
                         # coverage to mark it.
                         continue
 
+                if consider_as_foreign_keys and \
+                    fk.parent not in consider_as_foreign_keys:
+                    continue
                 if col is not None:
                     crit.append(col == fk.parent)
                     constraints.add(fk.constraint)
diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 test/orm/test_relationships.py
--- a/test/orm/test_relationships.py	Thu Feb 16 19:29:00 2012 -0500
+++ b/test/orm/test_relationships.py	Sat Feb 18 08:39:33 2012 -0500
@@ -202,7 +202,7 @@
             'company':relationship(Company, backref='employees'),
             'reports_to':relationship(Employee,
                 remote_side=[employee_t.c.company_id](employee_t.c.emp_id,),
-                foreign_keys=[employee_t.c.reports_to_id](employee_t.c.reports_to_id),
+                foreign_keys=[employee_t.c.company_id](employee_t.c.reports_to_id,),
                 backref=backref('employees', foreign_keys=None)
                 )
         })
@@ -240,7 +240,7 @@
                         (employee_t.c.reports_to_id, employee_t.c.emp_id),
                         (employee_t.c.company_id, employee_t.c.company_id)
                 ],
-                foreign_keys=[employee_t.c.reports_to_id](employee_t.c.reports_to_id),
+                foreign_keys=[employee_t.c.company_id](employee_t.c.reports_to_id,),
                 backref=backref('employees', foreign_keys=None)
                 )
         })"><pre class="notranslate"><code class="notranslate">diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 lib/sqlalchemy/orm/properties.py
--- a/lib/sqlalchemy/orm/properties.py	Thu Feb 16 19:29:00 2012 -0500
+++ b/lib/sqlalchemy/orm/properties.py	Sat Feb 18 08:39:33 2012 -0500
@@ -1030,7 +1030,8 @@
             # general mapped table, which in the case of inheritance is
             # a join.
             return join_condition(mapper.mapped_table, table, 
-                                        a_subset=mapper.local_table)
+                                        a_subset=mapper.local_table,
+                                        consider_as_foreign_keys=self._user_defined_foreign_keys)
 
         try:
             if self.secondary is not None:
diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 lib/sqlalchemy/sql/util.py
--- a/lib/sqlalchemy/sql/util.py	Thu Feb 16 19:29:00 2012 -0500
+++ b/lib/sqlalchemy/sql/util.py	Sat Feb 18 08:39:33 2012 -0500
@@ -225,7 +225,10 @@
 
     return visitors.cloned_traverse(crit, {}, {'binary':visit_binary})
 
-def join_condition(a, b, ignore_nonexistent_tables=False, a_subset=None):
+def join_condition(a, b, 
+                    ignore_nonexistent_tables=False, 
+                    a_subset=None,
+                    consider_as_foreign_keys=None):
     """create a join condition between two tables or selectables.
 
     e.g.::
@@ -269,6 +272,9 @@
                 else:
                     continue
 
+            if consider_as_foreign_keys and \
+                fk.parent not in consider_as_foreign_keys:
+                continue
             if col is not None:
                 crit.append(col == fk.parent)
                 constraints.add(fk.constraint)
@@ -286,6 +292,9 @@
                         # coverage to mark it.
                         continue
 
+                if consider_as_foreign_keys and \
+                    fk.parent not in consider_as_foreign_keys:
+                    continue
                 if col is not None:
                     crit.append(col == fk.parent)
                     constraints.add(fk.constraint)
diff -r 35cba3c84ab9c36663e57049e56e59cace5aa2f7 test/orm/test_relationships.py
--- a/test/orm/test_relationships.py	Thu Feb 16 19:29:00 2012 -0500
+++ b/test/orm/test_relationships.py	Sat Feb 18 08:39:33 2012 -0500
@@ -202,7 +202,7 @@
             'company':relationship(Company, backref='employees'),
             'reports_to':relationship(Employee,
                 remote_side=[employee_t.c.company_id](employee_t.c.emp_id,),
-                foreign_keys=[employee_t.c.reports_to_id](employee_t.c.reports_to_id),
+                foreign_keys=[employee_t.c.company_id](employee_t.c.reports_to_id,),
                 backref=backref('employees', foreign_keys=None)
                 )
         })
@@ -240,7 +240,7 @@
                         (employee_t.c.reports_to_id, employee_t.c.emp_id),
                         (employee_t.c.company_id, employee_t.c.company_id)
                 ],
-                foreign_keys=[employee_t.c.reports_to_id](employee_t.c.reports_to_id),
+                foreign_keys=[employee_t.c.company_id](employee_t.c.reports_to_id,),
                 backref=backref('employees', foreign_keys=None)
                 )
         })
</code></pre></div>
<p dir="auto">works great for the usual case of multiple foreign keys:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Project(Base):
    __tablename__ = 'project'
    id      = Column(String(100), primary_key=True)
    client_id = Column(String(80), ForeignKey('user.username'))
    creator_id = Column(String(80), ForeignKey('user.username'))
    workers = relationship(&quot;User&quot;, backref=&quot;assigned_project&quot;, 
                                foreign_keys=&quot;User.project_id&quot;,
                                )
    client  = relationship(&quot;User&quot;, backref=&quot;owned_projects&quot;, 
                                foreign_keys=client_id,
                                post_update=True)
    creator = relationship(&quot;User&quot;, backref=&quot;created_projects&quot;, 
                                foreign_keys=creator_id,
                                post_update=True)

class User(Base):
    __tablename__ = 'user'
    username            = Column(String(80),  primary_key=True)
    project_id = Column(String(100), ForeignKey(&quot;project.id&quot;, use_alter=True,
                                        name=&quot;fk_user_project_id&quot;))

e = create_engine(&quot;sqlite://&quot;, echo=True)
Base.metadata.create_all(e)
s = Session(e)

p1 = Project(id=&quot;someproject&quot;)
u1 = User(username=&quot;someuser&quot;)
p1.client = p1.creator = u1
u1.assigned_project = p1
s.add(u1)
s.commit()"><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Project(Base):
    __tablename__ = 'project'
    id      = Column(String(100), primary_key=True)
    client_id = Column(String(80), ForeignKey('user.username'))
    creator_id = Column(String(80), ForeignKey('user.username'))
    workers = relationship("User", backref="assigned_project", 
                                foreign_keys="User.project_id",
                                )
    client  = relationship("User", backref="owned_projects", 
                                foreign_keys=client_id,
                                post_update=True)
    creator = relationship("User", backref="created_projects", 
                                foreign_keys=creator_id,
                                post_update=True)

class User(Base):
    __tablename__ = 'user'
    username            = Column(String(80),  primary_key=True)
    project_id = Column(String(100), ForeignKey("project.id", use_alter=True,
                                        name="fk_user_project_id"))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)

p1 = Project(id="someproject")
u1 = User(username="someuser")
p1.client = p1.creator = u1
u1.assigned_project = p1
s.add(u1)
s.commit()
</code></pre></div>
<p dir="auto"><strong>Describe the bug</strong><br>
I've created a custom column type, which converts "Y" and "N" from within the database to <code class="notranslate">True</code> and <code class="notranslate">False</code> in the python context and vice versa. I choose to implement it as an enum type.</p>
<p dir="auto">This all works well. Now I've started to use naming convention for constraints and my oracle database suddenly complains about a duplicate constraint names.</p>
<p dir="auto">For debugging and finding a minimal example I used sqlite as the database engine and don't used naming convention (because it seem sqlite just overwrite the first appearance of a constraint name). The code below indeed creates two check constraints with the same content. Why?</p>
<p dir="auto"><strong>Expected behavior</strong></p>

<p dir="auto">Only one check constraint should be created.</p>
<p dir="auto"><strong>To Reproduce</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import enum
from pathlib import Path
from typing import Any, Type

from sqlalchemy import Column, Integer, String, create_engine, text, types
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class BoolYN(types.TypeDecorator):
    impl = types.Enum

    def __init__(self, *args: Any, **kwargs: Any):
        super().__init__(&quot;Y&quot;, &quot;N&quot;, *args, **kwargs)

    def process_literal_param(self, value: bool, dialect: Any) -&gt; str:
        &quot;&quot;&quot;
        Converts bool value passed to literal value on inserting to database.

        aka python --&gt; column
        &quot;&quot;&quot;
        if value is True:
            return &quot;Y&quot;
        elif value is False:
            return &quot;N&quot;
        else:
            raise TypeError(f&quot;{value} is not of type bool&quot;)

    process_bind_param = process_literal_param

    def process_result_value(self, value: str, dialect: Any) -&gt; bool:
        &quot;&quot;&quot;
        Convert combined enum value to associated single enum type.

        aka column --&gt; python
        &quot;&quot;&quot;

        if value == &quot;Y&quot;:
            return True
        elif value == &quot;N&quot;:
            return False
        else:
            raise ValueError(f&quot;{value} cannot be converted to a bool&quot;)


class User(Base):
    __tablename__ = &quot;users&quot;
    id = Column(Integer, primary_key=True)
    name = Column(String)
    active = Column(
        BoolYN(length=1, native_enum=False, create_constraint=True),
        nullable=False,
        server_default=text(&quot;'N'&quot;),
    )


def main():
    if Path(&quot;custom_type_db.db&quot;).exists():
        Path(&quot;custom_type_db.db&quot;).unlink()

    engine = create_engine(&quot;sqlite:///custom_type_db.db&quot;, echo=&quot;debug&quot;)

    Base.metadata.create_all(engine)


if __name__ == &quot;__main__&quot;:
    main()"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">enum</span>
<span class="pl-k">from</span> <span class="pl-s1">pathlib</span> <span class="pl-k">import</span> <span class="pl-v">Path</span>
<span class="pl-k">from</span> <span class="pl-s1">typing</span> <span class="pl-k">import</span> <span class="pl-v">Any</span>, <span class="pl-v">Type</span>

<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">String</span>, <span class="pl-s1">create_engine</span>, <span class="pl-s1">text</span>, <span class="pl-s1">types</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()


<span class="pl-k">class</span> <span class="pl-v">BoolYN</span>(<span class="pl-s1">types</span>.<span class="pl-v">TypeDecorator</span>):
    <span class="pl-s1">impl</span> <span class="pl-c1">=</span> <span class="pl-s1">types</span>.<span class="pl-v">Enum</span>

    <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-c1">*</span><span class="pl-s1">args</span>: <span class="pl-v">Any</span>, <span class="pl-c1">**</span><span class="pl-s1">kwargs</span>: <span class="pl-v">Any</span>):
        <span class="pl-en">super</span>().<span class="pl-en">__init__</span>(<span class="pl-s">"Y"</span>, <span class="pl-s">"N"</span>, <span class="pl-c1">*</span><span class="pl-s1">args</span>, <span class="pl-c1">**</span><span class="pl-s1">kwargs</span>)

    <span class="pl-k">def</span> <span class="pl-en">process_literal_param</span>(<span class="pl-s1">self</span>, <span class="pl-s1">value</span>: <span class="pl-s1">bool</span>, <span class="pl-s1">dialect</span>: <span class="pl-v">Any</span>) <span class="pl-c1">-&gt;</span> <span class="pl-s1">str</span>:
        <span class="pl-s">"""</span>
<span class="pl-s">        Converts bool value passed to literal value on inserting to database.</span>
<span class="pl-s"></span>
<span class="pl-s">        aka python --&gt; column</span>
<span class="pl-s">        """</span>
        <span class="pl-k">if</span> <span class="pl-s1">value</span> <span class="pl-c1">is</span> <span class="pl-c1">True</span>:
            <span class="pl-k">return</span> <span class="pl-s">"Y"</span>
        <span class="pl-k">elif</span> <span class="pl-s1">value</span> <span class="pl-c1">is</span> <span class="pl-c1">False</span>:
            <span class="pl-k">return</span> <span class="pl-s">"N"</span>
        <span class="pl-k">else</span>:
            <span class="pl-k">raise</span> <span class="pl-v">TypeError</span>(<span class="pl-s">f"<span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">value</span><span class="pl-kos">}</span></span> is not of type bool"</span>)

    <span class="pl-s1">process_bind_param</span> <span class="pl-c1">=</span> <span class="pl-s1">process_literal_param</span>

    <span class="pl-k">def</span> <span class="pl-en">process_result_value</span>(<span class="pl-s1">self</span>, <span class="pl-s1">value</span>: <span class="pl-s1">str</span>, <span class="pl-s1">dialect</span>: <span class="pl-v">Any</span>) <span class="pl-c1">-&gt;</span> <span class="pl-s1">bool</span>:
        <span class="pl-s">"""</span>
<span class="pl-s">        Convert combined enum value to associated single enum type.</span>
<span class="pl-s"></span>
<span class="pl-s">        aka column --&gt; python</span>
<span class="pl-s">        """</span>

        <span class="pl-k">if</span> <span class="pl-s1">value</span> <span class="pl-c1">==</span> <span class="pl-s">"Y"</span>:
            <span class="pl-k">return</span> <span class="pl-c1">True</span>
        <span class="pl-k">elif</span> <span class="pl-s1">value</span> <span class="pl-c1">==</span> <span class="pl-s">"N"</span>:
            <span class="pl-k">return</span> <span class="pl-c1">False</span>
        <span class="pl-k">else</span>:
            <span class="pl-k">raise</span> <span class="pl-v">ValueError</span>(<span class="pl-s">f"<span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">value</span><span class="pl-kos">}</span></span> cannot be converted to a bool"</span>)


<span class="pl-k">class</span> <span class="pl-v">User</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"users"</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>)
    <span class="pl-s1">active</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(
        <span class="pl-v">BoolYN</span>(<span class="pl-s1">length</span><span class="pl-c1">=</span><span class="pl-c1">1</span>, <span class="pl-s1">native_enum</span><span class="pl-c1">=</span><span class="pl-c1">False</span>, <span class="pl-s1">create_constraint</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
        <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>,
        <span class="pl-s1">server_default</span><span class="pl-c1">=</span><span class="pl-en">text</span>(<span class="pl-s">"'N'"</span>),
    )


<span class="pl-k">def</span> <span class="pl-en">main</span>():
    <span class="pl-k">if</span> <span class="pl-v">Path</span>(<span class="pl-s">"custom_type_db.db"</span>).<span class="pl-en">exists</span>():
        <span class="pl-v">Path</span>(<span class="pl-s">"custom_type_db.db"</span>).<span class="pl-en">unlink</span>()

    <span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite:///custom_type_db.db"</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-s">"debug"</span>)

    <span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">engine</span>)


<span class="pl-k">if</span> <span class="pl-s1">__name__</span> <span class="pl-c1">==</span> <span class="pl-s">"__main__"</span>:
    <span class="pl-en">main</span>()</pre></div>
<p dir="auto"><strong>Error</strong></p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/2164565/112795622-126c8600-9069-11eb-8b85-68edc80ef31b.png"><img src="https://user-images.githubusercontent.com/2164565/112795622-126c8600-9069-11eb-8b85-68edc80ef31b.png" alt="duplicate_constraint" style="max-width: 100%;"></a></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(&quot;users&quot;)
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,475 DEBUG sqlalchemy.engine.base.Engine Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk')
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(&quot;users&quot;)
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,475 DEBUG sqlalchemy.engine.base.Engine Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk')
2021-03-29 08:43:31,476 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	active VARCHAR(1) DEFAULT 'N' NOT NULL, 
	PRIMARY KEY (id), 
	CHECK (active IN ('Y', 'N')), 
	CHECK (active IN ('Y', 'N'))
)


2021-03-29 08:43:31,476 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,478 INFO sqlalchemy.engine.base.Engine COMMIT"><pre class="notranslate"><code class="notranslate">2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2021-03-29 08:43:31,474 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("users")
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,475 DEBUG sqlalchemy.engine.base.Engine Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk')
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("users")
2021-03-29 08:43:31,475 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,475 DEBUG sqlalchemy.engine.base.Engine Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk')
2021-03-29 08:43:31,476 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	active VARCHAR(1) DEFAULT 'N' NOT NULL, 
	PRIMARY KEY (id), 
	CHECK (active IN ('Y', 'N')), 
	CHECK (active IN ('Y', 'N'))
)


2021-03-29 08:43:31,476 INFO sqlalchemy.engine.base.Engine ()
2021-03-29 08:43:31,478 INFO sqlalchemy.engine.base.Engine COMMIT
</code></pre></div>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: Ubunto 18.04</li>
<li>Python: 3.9</li>
<li>SQLAlchemy: 1.3.23 / 1.4.2</li>
<li>Database: sqlite</li>
<li>DBAPI: sqlite3</li>
</ul>