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.
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 ;
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
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
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
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
nullis replaced internally with a
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
But to emphasize again: the underlying problem is that a primary key column cannot be
null, so your test would not matter anyway.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key