lundi 18 janvier 2016

mysql foreign key (Integrity constraint violation)

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