sql – Preventing both columns to be true in MySQL

I am using MySQL 5.7.

I have an organisation table. Each organisation has 2 contact method:

• ContactRepresentative
• UseBranchSpecificContact

I want to add a constraint to ensure not both of these flags are true (only one of these columns can be true ot thhey can both be false).

MySQL CHECK CONSTRAINT does not work in MySQL 5.7, so I though I can introduce a generated column called ValidContact_virtual and set its value to NULL if both contact methods are true… then I would add a NOT NULL constraint to ValidContact_virtual column:

CREATE TABLE organisation (
    OrganisationId              BIGINT          NOT NULL    AUTO_INCREMENT,
    OrganisationName            NVARCHAR(64)    NOT NULL,
    ContactRepresentative       BIT             NOT NULL DEFAULT 0,
    UseBranchSpecificContact    BIT             NOT NULL DEFAULT 0,
    ValidContact_virtual        BIT GENERATED ALWAYS AS (
        IF(ContactRepresentative = 1 AND UseBranchSpecificContact = 1, NULL, 1)
    ) VIRTUAL NOT NULL,
    
    
    CONSTRAINT PK_Organisation PRIMARY KEY(OrganisationId)
);

I have tried this solution, and it has worked so far… it does not allow adding a new organisation with both contact methods = 1 and it does not allow to update a record and set one of the contact methods to true when the other is already true

If there is an existing record, with both values = 1, then I cannot update that row… the only way to fix it, is to delete that record and add a new one.