MariaDB, Triggers and last_insert_id

Question

I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information).

CREATE TABLE `sample` (
  `sample_id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sample_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sample_name` (
  `sample_name_id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `project` varchar(255) NOT NULL,
  `sample_id` int unsigned NOT NULL,
  PRIMARY KEY (`sample_name_id`),
  FOREIGN KEY `fk_sample` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,
  UNIQUE KEY `un_sample_name` (`name`,`project`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `run` (
  `run_id` int unsigned NOT NULL AUTO_INCREMENT,
  `sample_id` int unsigned NOT NULL,
  PRIMARY KEY (`run_id`),
  FOREIGN KEY `fk_run1` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I would like to check if a sample (based on sample_name) exists or if not, then insert both and use the sample_id to insert run entry as well. To do this I planned first to check if the unique name (name + project) exists and if so use it. However if it doesn't, then use trigger to do double insertion (I have a perl script to do this logic; I tried also the commented update version).

delimiter |
CREATE TRIGGER tr_new_sample BEFORE INSERT ON sample_name FOR EACH ROW BEGIN
  IF NEW.sample_id IS NULL THEN
    INSERT INTO sample () VALUES ();
    -- UPDATE sample_name SET NEW.sample_id = LAST_INSERT_ID();
    SET NEW.sample_id := (SELECT LAST_INSERT_ID());
  END IF;
END |
delimiter ;

So when sample name does not exists, I try to insert a record with null sample_id and thus the trigger reacts. However this doesn't work and I get a following error. Can someone suggest on how to fix this or make it work.

The database is actually MariaDB 5.2.12.

Thanks,

mysql> INSERT INTO sample_name (name,project) VALUES ('a','a_project');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pipeline_runs`.`sample_name`, CONSTRAINT `sample_name_ibfk_1` FOREIGN KEY (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE)

Answer 1

I try to insert a record with null sample_id

`sample_id` int unsigned NOT NULL,

The sample_id can't be null. You will probably find NEW.sample_id = 0 inside the trigger if you don't specify a value on insert, since you have declared the column as NOT NULL... so you need to test for that instead:

IF NEW.sample_id = 0 THEN
...

...or change your table definition to allow null values.

---

Update (for the benefit of future visitors):

MySQL Bug #6295 (yes, from 9 years ago) is resolved in MySQL 5.7.1 and apparently changes this behavior so that NOT NULL validation is not imposed until after the BEFORE trigger is processed. This behavior will likely make its way into MariaDB at some point also. If the original poster of this question is using the = 0 test that currently works, it's possible that this behavior change could cause that logic to fail since the timing (or occurrence, at all) of the coercion to from NULL to 0 might also change.

If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug #6295, Bug #11744964)

^{— http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html}

See also: http://dev.mysql.com/worklog/task/?id=6030