Level 29
REFERENCES ticket_replies (ticket_id) ??
How have you defined the foreign constraint and why ????
Jan 18, 2016
7
Level 13
database foreign key problem (foreign key constraint fails)
I've got a table called tickets. design looks like: http://puu.sh/mAKLK/3e3eb90428.png
The other table called ticket_replies looks like: http://puu.sh/mAKQC/7f24cd0c1a.png
The "tickets" table contains a foreign key to the "ticket_replies" table. This foreign key is constructed like this: http://puu.sh/mAL2Z/44aa5c39bd.png
In my controller I want to use this code:
//create new ticket
$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();
This results in an error 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 can understand why this occurs but as you may see on my table design, there isn't a way to prevent this, because I need the "generated" id of ticket in my ticket_replie. -Any help desirabled ;)
Level 13
Take a look here to see design of foreign key.
http://puu.sh/mAL2Z/44aa5c39bd.png
I would like to have my database all the time correct, so nobody can insert a ticket_replie without creating a ticket before. When I delete a ticket the "linked" ticket_replies should be deleted too.
Level 29
@jaheller A little more theory is required here. I am not posting the solution on purpose but the link which should help you find it.
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Level 13
The foreign key I added looks like this:
ALTER TABLE `ticket_replies` ADDFOREIGN KEY (`ticket_id`)
REFERENCES `sampleauth`.`tickets`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Now when I delete a "ticket" in the "ticket" table it gets deleted in "ticket_replies" too. The other way this doesn't work, all in all I would like this to work the other way too, so my database has all the time consistency. How to do so?
Level 29
You can't have the other way working, as when you delete the ticket_reply it deletes the ticket as well. However, you can enforce certain behavior
Level 13
So as far as I understand your anser there is no possiblity to "denie" the delete of a ticket_replie? When I set the foreign key to on delete "restrict" I can't delete a ticket as long as a matching ticket_replie exists. Not the way I needed to have the restriction.
Level 29
Sorry dude, it becoming less clear to me of what you want.
With foreign keys,
1> Ensure that a ticket exists before a reply is inserted.
2> Delete all the replies when a ticket is deleted.
3> Restrict the ticket to be deleted, if even one reply exists.
Is what you want, not included in above three?
Please or to participate in this conversation.