如何在SQLAlchemy中映射非唯一非键列至其他表？

1. 先明确外键的硬性要求 #

MySQL的外键约束有个死规矩：被引用的列必须是主键或者带唯一约束的列。你之前单独拿ordinal_position当外键引用，它本身不是唯一键，数据库没法保证你引用的是哪条记录，所以errno:150是必然报错。

2. 建联合外键才是正解 #

既然TaskEntity里唯一能定位一条记录的是task_no + ordinal_position这个组合，那TaskLog必须同时把这两个字段都存下来，建立联合外键。

数据库建表示例（简化版） #

先看TaskEntity的表结构：

CREATE TABLE TaskEntity (
    job_no VARCHAR(50) NOT NULL,
    task_no VARCHAR(50) NOT NULL,
    ordinal_position INT NOT NULL,
    -- 其他业务字段
    PRIMARY KEY (job_no, task_no), -- 假设job_no+task_no是主键
    UNIQUE KEY idx_task_ordinal (task_no, ordinal_position) -- 必须加这个联合唯一约束
);

然后TaskLog要同步包含这两个字段，再建联合外键：

CREATE TABLE TaskLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    task_no VARCHAR(50) NOT NULL,
    task_ordinal_position INT NOT NULL,
    -- 其他日志字段
    FOREIGN KEY fk_task_log_task (task_no, task_ordinal_position)
        REFERENCES TaskEntity(task_no, ordinal_position)
        ON DELETE CASCADE ON UPDATE CASCADE
);

3. ORM层面（以SQLAlchemy为例）的正确写法 #

如果用ORM框架，不能只关联单个字段，得基于联合键做映射，同时把约束定义清楚：

TaskEntity模型 #

from sqlalchemy import Column, String, Integer, UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class TaskEntity(Base):
    __tablename__ = 'task_entity'
    job_no = Column(String(50), nullable=False)
    task_no = Column(String(50), nullable=False, primary_key=True)
    ordinal_position = Column(Integer, nullable=False)
    
    # 强制task_no+ordinal_position唯一
    __table_args__ = (
        UniqueConstraint('task_no', 'ordinal_position', name='idx_task_ordinal'),
    )

TaskLog模型 #

class TaskLog(Base):
    __tablename__ = 'task_log'
    log_id = Column(Integer, autoincrement=True, primary_key=True)
    task_no = Column(String(50), nullable=False)
    task_ordinal_position = Column(Integer, nullable=False)
    
    # 定义联合外键关联
    __table_args__ = (
        ForeignKeyConstraint(
            ['task_no', 'task_ordinal_position'],
            ['task_entity.task_no', 'task_entity.ordinal_position'],
            name='fk_task_log_task'
        ),
    )
    
    # 建立关联关系，必须同时匹配两个字段
    task = relationship(
        "TaskEntity",
        primary_join="and_(TaskLog.task_no == TaskEntity.task_no, TaskLog.task_ordinal_position == TaskEntity.ordinal_position)",
        backref="logs"
    )

4. 映射异常的排查要点 #

如果用relationship后数据还是映射不对，先查这几点：

• 检查TaskLog和TaskEntity里的task_no、ordinal_position字段，数据类型、长度必须完全一致（比如不能一个是VARCHAR(50)一个是VARCHAR(30)）
• 确认TaskEntity的task_no + ordinal_position联合唯一约束真的建好了，没写错字段名
• 关联条件必须同时包含两个字段，别漏了任何一个
• 查询的时候，要同时用两个字段做匹配，不能只查ordinal_position

内容的提问来源于stack exchange，提问作者Wonseok Choi