Issue 1:
Migrated issue, originally created by Michael Bayer (@zzzeek)

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.

Might have to push this to 0.8.

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)
)
})

works great for the usual case of multiple foreign keys:

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()

Issue 2:
Describe the bug

I've created a custom column type, which converts "Y" and "N" from within the database to True and False in the python context and vice versa. I choose to implement it as an enum type.

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.

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?

Expected behavior

Only one check constraint should be created.

To Reproduce

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__("Y", "N", *args, **kwargs)

def process_literal_param(self, value: bool, dialect: Any) -> str:
"""
Converts bool value passed to literal value on inserting to database.

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

process_bind_param = process_literal_param

def process_result_value(self, value: str, dialect: Any) -> bool:
"""
Convert combined enum value to associated single enum type.

aka column --> python
"""

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

class User(Base):
__tablename__ = "users"
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("'N'"),
)

def main():
if Path("custom_type_db.db").exists():
Path("custom_type_db.db").unlink()

engine = create_engine("sqlite:///custom_type_db.db", echo="debug")

Base.metadata.create_all(engine)

if __name__ == "__main__":
main()

Error

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

Versions.

OS: Ubunto 18.04

Python: 3.9

SQLAlchemy: 1.3.23 / 1.4.2

Database: sqlite

DBAPI: sqlite3