Duplicated table constraint names for tables using naming convention and extending from an abstract table #10441
Description
Describe the bug
When creating multiple tables which inherit from an abstract table, table constraints which use the table_name token are assigned the value of the first table name. I.e. If there is an abstract class MyBaseTable which defines a check constraint to be ck_%(table_name)s_%(constraint_name)s, the expectation is that for tables alpha and beta which inherit from MyBaseTable, that the constraints would be named ck_alpha_my_constraint_name and ck_beta_my_constraint_name, but the result is ck_alpha_my_constraint_name for both tables.
In SQL Server, this causes an error, because a constraint name must be unique within the database.
The stack trace below is from where it generates an error in SQL Server and was run on SQLAlchemy 1.4.49. The code to reproduce the error uses SQLite and was run on SQLAlchemy 2.0.21, which does not generate an error, but the problem can be seen in the output of this code provided under "Additional Context", where ck_alpha_my_constraint should have been ck_beta_my_constraint.
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
https://docs.sqlalchemy.org/en/20/core/constraints.html#configuring-constraint-naming-conventions
SQLAlchemy Version in Use
2.0.21 and 1.4.49
DBAPI (i.e. the database driver)
pyodbc and sqlite3
Database Vendor and Major Version
SQL Server 2016 and SQLite 3
Python Version
3.10
Operating system
Linux
To Reproduce
from sqlalchemy import (
create_engine,
Integer,
Column,
CheckConstraint,
MetaData,
)
from sqlalchemy.orm import declarative_base
# As found in the example at https://docs.sqlalchemy.org/en/20/core/constraints.html#configuring-a-naming-convention-for-a-metadata-collection
constraint_naming_conventions = {
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s",
}
metadata_obj = MetaData(naming_convention=constraint_naming_conventions)
Base = declarative_base(metadata=metadata_obj)
engine = create_engine("sqlite://", echo=True)
class MyBaseTable(Base):
__abstract__ = True
__table_args__ = (
CheckConstraint("a > 0 OR b < 100", name='my_constraint'),
)
id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
a = Column(Integer, nullable=False)
b = Column(Integer, nullable=False)
class ConcreteTableAlpha(MyBaseTable):
__tablename__ = 'alpha'
class ConcreteTableBeta(MyBaseTable):
__tablename__ = 'beta'
if __name__ == '__main__':
Base.metadata.create_all(engine, checkfirst=True)Error
Traceback (most recent call last):
File "/home/wessven/venv/my_venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/home/wessven/venv/my_venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42S01', "[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'ck_alpha_my_constraint' in the database. (2714) (SQLExecDirectW)")
Additional context
2023-10-09 10:27:56,811 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-10-09 10:27:56,812 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("alpha")
2023-10-09 10:27:56,812 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-10-09 10:27:56,812 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("alpha")
2023-10-09 10:27:56,812 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-10-09 10:27:56,812 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("beta")
2023-10-09 10:27:56,812 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-10-09 10:27:56,813 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("beta")
2023-10-09 10:27:56,813 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-10-09 10:27:56,813 INFO sqlalchemy.engine.Engine
CREATE TABLE alpha (
id INTEGER NOT NULL,
a INTEGER NOT NULL,
b INTEGER NOT NULL,
CONSTRAINT pk_alpha PRIMARY KEY (id),
CONSTRAINT ck_alpha_my_constraint CHECK (a > 0 OR b < 100)
)
2023-10-09 10:27:56,813 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2023-10-09 10:27:56,814 INFO sqlalchemy.engine.Engine
CREATE TABLE beta (
id INTEGER NOT NULL,
a INTEGER NOT NULL,
b INTEGER NOT NULL,
CONSTRAINT pk_beta PRIMARY KEY (id),
CONSTRAINT ck_alpha_my_constraint CHECK (a > 0 OR b < 100)
)
2023-10-09 10:27:56,814 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2023-10-09 10:27:56,814 INFO sqlalchemy.engine.Engine COMMIT