Why Does My SQL Trigger That Simulates An XOR Not Work?

I want to have a condition that either the artist or the event in an assignment is null, but it rejects an assignment even though it has an artist.

The trigger:

delimiter $$
CREATE TRIGGER assignment_event_or_artist BEFORE INSERT ON assignment
FOR EACH ROW 
BEGIN 
    IF(
        (NEW.event IS NULL AND NEW.artist IS NULL) || 
        (NEW.event IS NOT NULL AND NEW.artist IS NOT NULL)
    )
    THEN
        SIGNAL SQLSTATE '44000'
            SET MESSAGE_TEXT = 'new assignment does not have an event xor an artist';
    END IF;
END$$
delimiter ;

The assignment:

INSERT INTO assignment(edition, festival, artist, category) VALUES ("2021", "Le Guess Who?", "Bent Arcana", "SOLL");

The error code:

ERROR 1644 (44000) at line 487: new assignment does not have an event xor an artist

What am I doing wrong?

EDIT: As solarflare pointed out in his answer, the table definition for the assignment is important, so I'm including it here:

CREATE TABLE assignment ( edition YEAR NOT NULL, festival VARCHAR(200) NOT NULL, event INT, artist VARCHAR(200), category VARCHAR(200), PRIMARY KEY (edition, festival, event, artist);

According to your comment, both event and artist are part of the primary key:

CREATE TABLE assignment ( 
     edition YEAR NOT NULL, 
     festival VARCHAR(200) NOT NULL, 
     event INT, 
     artist VARCHAR(200), 
     category VARCHAR(200), 
     PRIMARY KEY (edition, festival, event, artist), ... )

Primary keys cannot contain null values, and if you don't provide a default value, you would usually get the error message

Field 'event' doesn't have a default value

If you you want to be able to have a situation where either event or artist have to be null, you would need to fix your primary key.

To explain why you don't get that error, but your signal message, there are some possible reasons:

• If you do not enable strict sql mode, MySQL replaces missing values with implicit defaults (in this case, it replaces the null with a 0, so both columns are not null in your trigger)

• Even if you set strict mode, MySQL up to 5.6 uses an implicit default for primary key colums that are not specificed not null (which matches your table definition you are using):

Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as a NOT NULL column (because PRIMARY KEY columns must be NOT NULL), but also assigns it a DEFAULT clause using the implicit default value.

• if you are using MariaDB, it may be a side effect of an unfixed MariaDB bug (which is caused by a similar behaviour where null is replaced internally with a 0).

So by one of these (or similar) effects, inside the trigger, event has been given the value 0, so both columns are not null and thus fulfilling your if condition. You could actually test for 0 in that if statement.

But to emphasize again: the underlying problem is that a primary key column cannot be null, so your test would not matter anyway.