php - mysql 外键(违反完整性约束)

Question

我正在开发一个小型支持(票务)系统。我的表是 Tickets 和 Ticket_replies。

门票表的设计是

id|user_id|title|...

ticket_replies 的设计如下:

id|ticket_id|...

现在我想创建一个从门票表到ticket_replies表的外键。此外键应保护 Ticket_replies 表不被编辑，而无需事先编辑 Ticket 表。例如，如果票证的 id 发生更改，则“ticket_replies”中的 Ticket_id 也应该更改。如果票证中的票证被删除，它也应该在“ticket_replies”中删除。

我添加的外键如下所示:

ALTER TABLE `tickets` ADD FOREIGN KEY (`id`) REFERENCES `sampleauth`.`ticket_replies`(`ticket_id`) ON DELETE CASCADE ON UPDATE CASCADE;

创建外键成功，但是当我尝试插入如下数据时:

            $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();

它失败了:

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))

我明白为什么会发生这种情况(因为当我创建新票证时，Mysql 会检查 Ticket_replies 中的 Ticket_id 是否与 Ticket_id 匹配)，但我不知道如何解决这个问题，有什么想法吗？

Answer 1

这些会起作用

ALTER TABLE `tickets` DROP FOREIGN KEY `ticket_ibfk_1`;
ALTER TABLE ticket_replies ADD FOREIGN KEY (ticket_id) REFERENCES ticket(id) ON DELETE CASCADE ON UPDATE CASCADE;

这些是我运行的完整测试代码。

CREATE TABLE `tickets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `ticket_replies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` int(10) unsigned NOT NULL,
  `reply` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

ALTER TABLE ticket_replies ADD FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE ON UPDATE CASCADE;

insert into tickets(name) values('123');
insert into ticket_replies(ticket_id, reply) values(LAST_INSERT_ID(),'123');