MySQL best way to define a couple of columns as both null or both not-null

constraintMySQLnulltrigger

On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns are not null. Is there any specific constraint, or could I do it just via BEFORE INSERT / BEFORE UPDATE triggers?

In other words, if a column of a record is null and it is updated setting it with a value, also the other column of that record must be updated with a not-null value. If the column is not null already, I can update it with another value (optionally, the other one too), but if I set it as null, the other column too must be set as null by the update query (no "default" settings).

Thank you.

Best Answer

I should think a table-level CHECK constraint would serve here, something like

CONSTRAINT c1_c2_null_match
  CHECK ((c1 IS NULL AND c2 IS NULL)
     OR  (c1 IS NOT NULL AND c2 IS NOT NULL))

See the MySQL 8.0 documentation.

You could instead use a trigger, but can run into consistency issues that way. Certain bulk-load operations will bypass triggers, meaning that the check is never performed at all and allowing the condition to be violated. A constraint can be temporarily disabled, but the check will be performed when the constraint is re-enabled.

One note: You don't mention which version of MySQL you're using, but prior to 8.0.16 CHECK constraints were ignored, so you would need to use a trigger to do what you want.

To do this with a trigger you need to return an error if the fields don't match, something like this:

DELIMITER //
CREATE TRIGGER c1_c2_null_match BEFORE UPDATE ON tbl
  FOR EACH ROW
  BEGIN
    IF (NEW.c1 IS NULL AND NEW.c2 IS NOT NULL)
    OR (NEW.c1 IS NOT NULL AND NEW.c2 IS NULL)
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Null columns must match';
    END IF;
  END //
DELIMITER;

See this Stack Overflow question.

Hope that helps.

Related Solutions

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

If you have a definition like this:

create table foo 
(
   id integer not null
)

The following query will show "NO" in the is_nullable column:

select table_name, 
       column_name,
       is_nullable
from information_schema.columns

If the column has a check constraint, like this:

create table foo
(
  id integer,
  constraint id_not_null check (id is not null)
);

then this will be shown as "nullable" with the first query. You would need to check information_schema.constraints and analyze the content of the CHECK_EXPRESSION column to detect this.

Sql-server – Unique Constraint with multiple null columns

I think your database is suffering from a bad data model. You might want to rethink it a little, judging from the tables you've mentioned the model isn't well normalized and you're trying to enforce a foreign key reference to a table column which isn't really a key to anything. One way to go is something like this :

CREATE TABLE EMP (ID INT PRIMARY KEY IDENTITY
                 ,EmpName VARCHAR(100) NULL)

CREATE TABLE PCTypes (id INT PRIMARY KEY IDENTITY
                     ,PcTypeName VARCHAR(100) NULL)

CREATE TABLE PC (Id INT PRIMARY KEY IDENTITY
                ,EmpId INT NULL
                ,PCName VARCHAR(100) NULL
                ,HostName VARCHAR(100) NULL
                ,PhysicalLocation VARCHAR(100) NULL
                ,PCType INT NULL)

ALTER TABLE PC ADD CONSTRAINT FK_PC_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)
ALTER TABLE PC ADD CONSTRAINT FK_PC_PCType FOREIGN KEY (PCType) REFERENCES PCTypes(id)

CREATE TABLE PCNetwork (Id INT PRIMARY KEY IDENTITY
                       ,NetworkName VARCHAR(100) NULL
                       ,EmpId INT NULL
                       ,PhysicalLocation VARCHAR(100) NULL)

ALTER TABLE PCNetwork ADD CONSTRAINT FK_PCNetwork_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)

CREATE TABLE PCNetwork_PCs (PCNetworkId INT NOT NULL
                           ,PCId INT NOT NULL
                           ,IPAddr NVARCHAR(20) NULL)

ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_Network FOREIGN KEY (PCNetworkId) REFERENCES PCNetwork(id)
ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_PC FOREIGN KEY (PCId) REFERENCES PC(id)
CREATE UNIQUE INDEX uq_pcnetworks ON PCNetwork_PCs(PCNetworkId, PCId)
CREATE UNIQUE INDEX uq_pcnetworks_ip ON PCNetwork_PCs(IPAddr)

Best Answer

Related Solutions

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

Sql-server – Unique Constraint with multiple null columns

Related Question