Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

1 Answer

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

share|improve this answer
    
That works, thank you. Went with the '= 0' version. I was thinking that since the trigger is meant to go off before insert, it would have been done before NOT NULL constraint is checked. But it seems not. –  user21156 Mar 12 '13 at 7:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.