I am using MySQL 5.7.
I have an
organisation table. Each
organisation has 2 contact method:
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
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
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
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.