I'm working on a little support (ticket) system. My tables are tickets and ticket_replies.
Design of tickets table is
|id|user_id|title|...
Design of ticket_replies looks like:
|id|ticket_id|...
Now I would like to create a foreign key from the tickets table to the ticket_replies table. This foreign key should protect the ticket_replies table from being edited without editing the ticket table before. Like if for example the id of the ticket changes the ticket_id should be changed in "ticket_replies" too. If a ticket is deleted in tickets it should be deleted in "ticket_replies" too.
The foreign key I added looks like this:
ALTER TABLE `tickets` ADDFOREIGN KEY (`id`) REFERENCES `sampleauth`.`ticket_replies`(`ticket_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Creating this foreign key is successful, but when I try to insert data like:
$ticket = new Ticket;
$ticket->user_id = $user->id;
$ticket->title = $request->title;
$ticket->status = 0;
$ticket->department_id = $request->departments;
$ticket->save();
//create new ticket_replie
$ticket_replie = new Ticket_replie;
$ticket_replie->ticket_id = $ticket->id;
$ticket_replie->user_id = $user->id;
$ticket_replie->text = $request->question;
$ticket_replie->save();
It fails saying:
Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (sampleauth.tickets, CONSTRAINT tickets_ibfk_1 FOREIGN KEY (id) REFERENCES ticket_replies (ticket_id) ON DELETE CASCADE ON UPDATE CASCADE) (SQL: insert into tickets (user_id, title, status, department_id, updated_at, created_at) values (1, sasa, 0, 1, 2016-01-18 23:03:21, 2016-01-18 23:03:21))
I understand why this occurs (because when I create a new ticket mysql checks if ticket_id from ticket_replies matches the ticket_id), but I have no idea how to solve this, any idea?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire