gpt4 book ai didi

mysql - 如何进行所有插入然后检查外键约束? (SQL 事务因外键约束而失败)

转载 作者:行者123 更新时间:2023-11-29 02:22:55 25 4
gpt4 key购买 nike

我有两个表:

enter image description here

每个牌组(纸牌)总是属于某个玩家(一个玩家可以拥有 1..* 牌组)。此外,每个玩家总是有一个(而且只有一个)活跃的套牌。

我想添加一个新玩家,但是如果我只是执行 INSERT INTO players(...) VALUES (...),我将不会有 active_deck_id 应该插入到这个查询中。同样,如果我从 decks 开始,我将不会有 player_id

这就是我决定使用事务(在过程中)的原因:

CREATE PROCEDURE `add_player` (IN login VARCHAR(255), IN password VARCHAR(255), 
OUT player_id INT(11))
BEGIN
START TRANSACTION;
INSERT INTO `players` (id, `login`, `password`, registered, last_logged, active_deck_id)
VALUES ('', login, pass, now(), now(), 0);

SET player_id = LAST_INSERT_ID();
INSERT INTO `decks` (id, name, player_id) VALUES ('', `sample deck`, player_id);

COMMIT;
END
END

然后我刚刚调用它:

set @login = 'mylogin';
set @pass = 'pass';
call add_player(@login, @pass, @id);

...但是我得到一个错误:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`game`.`players`, CONSTRAINT `active_deck` FOREIGN KEY (`active_deck_id`) REFERENCES `decks` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) 

那么,如何进行所有插入,然后检查外键约束?因为在第一次插入之后,约束应该被破坏,但在整个事务之后它们应该没问题。 p>

我的表的 SQL:

CREATE TABLE IF NOT EXISTS `game`.`decks` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`player_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`, `player_id`),
INDEX `player` (`player_id` ASC),
CONSTRAINT `player`
FOREIGN KEY (`player_id`)
REFERENCES `game`.`players` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `game`.`players` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`login` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`registered` DATETIME NOT NULL,
`last_logged` DATETIME NOT NULL,
`active_deck_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `active_deck` (`active_deck_id` ASC),
CONSTRAINT `active_deck`
FOREIGN KEY (`active_deck_id`)
REFERENCES `game`.`decks` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

最佳答案

试试这个命令

设置 FOREIGN_KEY_CHECKS = 0;

在插入之前,将值设置为 1 将其打开。

关于mysql - 如何进行所有插入然后检查外键约束? (SQL 事务因外键约束而失败),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28310178/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com