Check constraint for mutually exclusive columns in SQLAlchemy

Question

If I have a SQLAlchemy declarative model like below:

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

And I would like to ensure that only one of the *test_id foreign keys is present in a given row, how might I accomplish that in SQLAlchemy?

I see that there is an SQLAlchemy CheckConstraint object (see docs), but MySQL does not support check constraints.

The data model has interaction outside of SQLAlchemy, so preferably it would be a database-level check (MySQL)

Answer 1

Well, considering your requisites "The data model has interaction outside of SQLAlchemy, so preferably it would be a database-level check (MySQL)" and 'ensure that only one [..] is not null'. I think the best approach is to write a trigger like this:

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 ;

Some tricks and considerations:

1. IF STATEMENT: In order to avoid the tedious writing of check each column is not null while others are null, I did this trick: Reduce each column to one character and check how many characters exist. Note that NEW.a-NEW.a always returns 1 character if NEW.a is an Integer, NULL returns 0 characters and the operation NULL-NULL returns NULL on MySQL.

2. ERROR TRIGGERING: I suppose you want to raise an error, so how to do this on MySQL? You didn't mention the MySQL version. Only on MySQL 5.5 you can use the SIGNAL syntax to throw an exception. So the more portable way is issuing an invalid statement like: UPDATE xx SET z=0. If you are using MySQL 5.5 you could use: signal sqlstate '45000' set message_text = 'Error: Only one value of *test_id must be not null'; instead of UPDATE `Error: Only one value of *test_id must be not null` SET z=0;

Also, I think you want to check this on updates too, so use:

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 ;

Or create a stored procedure and call it.

Update

For databases that supports check constraints, the code is more simple, see this example for 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