CODEWITHSUNDEEP
mysql
Debargha Roy
Debargha Roy

Reputation: 2698

Error: 1452 - Insert operation failed after adding trigger

I have the following schema:

user table:

+---------------+--------------+------+-----+-------------------+-------------------+
| Field         | Type         | Null | Key | Default           | Extra             |
+---------------+--------------+------+-----+-------------------+-------------------+
| user_id       | int          | NO   | PRI | NULL              | auto_increment    |
| account_type  | varchar(10)  | NO   |     | NULL              |                   |
+---------------+--------------+------+-----+-------------------+-------------------+

student table:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| student_id     | int          | NO   | PRI | NULL    | auto_increment |
| user_id        | int          | YES  | MUL | NULL    |                |
| specialization | varchar(50)  | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

The user_id in student table is a foreign key from user table. Adding data to both tables in order was working well, until I introduced this trigger

create trigger after_user_insert
-> after insert on user for each row
-> begin
-> if new.account_type like 'student' then
-> insert into student(user_id) values (LAST_INSERT_ID());
-> end if;
-> end;//

Now, it gives

error 1452: (23000): Cannot add or update a child row: a foreign key constraint fails (studentmanager.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (user_id) REFERENCES user (user_id))

Upvotes: 0

Views: 122

Answers (1)

ikiK
ikiK

Reputation: 6532

This really took me a while to get my head around hehe.

CREATE TRIGGER after_user_insert
AFTER insert ON user
FOR EACH ROW
insert into student(user_id) SELECT (user_id) FROM user 
WHERE new.account_type LIKE 'student' ORDER BY user_id DESC LIMIT 1;

RESULT:

Schema (MySQL v8.0)

**Query #1**

    SELECT * FROM user;

| user_id | account_type |
| ------- | ------------ |
| 1       | student      |
| 2       | student      |
| 3       | not          |
| 4       | student      |
| 5       | student      |
| 6       | not          |
| 7       | not          |
| 8       | student      |

---
**Query #2**

    SELECT * FROM student;

| student_id | user_id |
| ---------- | ------- |
| 1          | 1       |
| 2          | 2       |
| 3          | 4       |
| 4          | 5       |
| 5          | 8       |

---

View this working example on DB Fiddle

I'm no expert and I do this for fun so someone please correct me if I'm wrong. I believe you can not have LIKE without SELECT. As well as LAST_INSERT_ID(). And i forgot in process of this what was wrong with keys, but there was obliviously couple of things wrong here. I THINK.

I wouldn't mind second opinion about my query also. But as much as I tested it is correct.

Hope it helps.

Upvotes: 2

Related Questions