SQLAlchemy中用于互斥列的检查约束

Question

如果我有一个类似下面的SQLAlchemy声明模型：

class Test(Model):
    __tablename__ = 'tests'

    id = Column(Integer, Sequence('test_id_seq'), primary_key=True)
    ...
    Atest_id = Column(Integer, ForeignKey('Atests.id'), nullable=True)
    Btest_id = Column(Integer, ForeignKey('Btests.id'), nullable=True)
    Ctest_id = Column(Integer, ForeignKey('Ctests.id'), nullable=True)
    Dtest_id = Column(Integer, ForeignKey('Dtests.id'), nullable=True)
    Etest_id = Column(Integer, ForeignKey('Etests.id'), nullable=True)
    ...
    date = Column(DateTime)
    status = Column(String(20))  # pass, fail, needs_review

我希望确保在给定行中只存在一个*test_id外键，我该如何在SQLAlchemy中实现？

我看到有一个SQLAlchemy的CheckConstraint对象(请参见文档),但是MySQL不支持检查约束。

数据模型与SQLAlchemy之外进行交互，因此最好是在数据库级别上进行检查（MySQL）。

- Brian Leach

---

请注意，一些数据库不会主动支持检查约束，例如MySQL。 - 7stud

对于mysql，请参见此处：http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html， http://dba.stackexchange.com/questions/43284/two-nullable-columns-one-required-to-have-value - 7stud

@7stud更新了问题以反映出来。 - Brian Leach

@7stud 第二个链接看起来很适用，不过我想知道是否有一种方法可以构建那个逻辑以优雅地处理n列的检查。 - Brian Leach

Answer 1

根据您的要求 "数据模型在SQLAlchemy之外有交互，因此最好进行数据库级别的检查（MySQL）" 以及 '确保仅有一个[..]不为空'。我认为最好的方法是编写以下触发器：

DELIMITER $$
CREATE TRIGGER check_null_insert BEFORE INSERT 
ON my_table
FOR EACH ROW BEGIN
IF CHAR_LENGTH(CONCAT_WS('', NEW.a-NEW.a, NEW.b-NEW.b, NEW.c-NEW.c)) = 1 THEN 
    UPDATE `Error: Only one value of *test_id must be not null` SET z=0;
END IF;
END$$
DELIMITER ;

一些技巧和注意事项：

1. IF语句：为了避免繁琐的检查每一列是否为null，我使用了这个技巧：将每一列缩减为一个字符并检查有多少个字符存在。请注意，如果NEW.a是一个Integer，NEW.a-NEW.a始终返回1个字符，NULL返回0个字符，而MySQL上的NULL-NULL操作返回NULL。

2. 错误触发：我假设您想引发一个错误，那么如何在MySQL上实现？您没有提到MySQL版本。只有在MySQL 5.5上可以使用SIGNAL语法来抛出异常。因此，更可移植的方法是发出无效语句，例如：UPDATE xx SET z=0。如果您正在使用MySQL 5.5，则可以使用以下语句代替UPDATE `Error: Only one value of *test_id must be not null` SET z=0;：signal sqlstate '45000' set message_text = 'Error: Only one value of *test_id must be not null';

此外，我认为您还想在更新时进行检查，因此请使用：

DELIMITER $$
CREATE TRIGGER check_null_update BEFORE UPDATE 
ON my_table
FOR EACH ROW BEGIN
IF CHAR_LENGTH(CONCAT_WS('', NEW.a-NEW.a, NEW.b-NEW.b, NEW.c-NEW.c)) = 1 THEN 
    UPDATE `Error: Only one value of *test_id must be not null` SET z=0;
END IF;
END$$
DELIMITER ;

或者创建一个存储过程并调用它。

更新

对于支持检查约束的数据库，代码更简单，请参阅此 SQL Server 示例：

CREATE TABLE MyTable (col1 INT NULL, col2 INT NULL, col3 INT NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK (
  LEN(CONCAT(col1-col1, col2-col2, col3-col3)) = 1
)
GO

- iuridiniz