插入时出现 Mysql 完整性错误 1452

Question

select * from memory_games_game; 给我下表:

select * from memory_games_game_state;给我下表:

我有一个如下所示的存储过程:

delimiter //
CREATE PROCEDURE get_game_by_user_id(  
     p_user_id int, p_game_id int  
)   
BEGIN  
    insert into memory_games_game_state(user_id, game_id, level, progress) 

    SELECT 24 as user_id,
        game.game_type as game_id,
        1 as level,
        0 as progress

    FROM memory_games_game game 
    left outer join memory_games_game_state gameState on 
        game.game_type=gameState.game_id  and
        gameState.user_id=24 
    where game.level=1 and gameState.user_id is null;

    if p_game_id = -1 then
        SELECT  gameState.level, game_type, `current_date`    
        FROM memory_games_game game join memory_games_game_state gameState on 
             game.game_type=gameState.game_id and 
             gameState.user_id=24 and
             game.level=gameState.level;
    else
    SELECT gameState.level, game_type, `current_date`   
    FROM memory_games_game game join memory_games_game_state gameState on 
        game.game_type=gameState.game_id and 
        gameState.user_id=p_user_id and
        game.level=gameState.level
        WHERE game_type=12;

    end if;
END
// 

第一个插入将记录插入到memory_games_game_state表中。

此插入对于 game_id 从 8 到 11 是成功的，但是对于 12 失败并出现以下错误:

我可以在表 memory_games_game 中插入 game_type 12 的记录，它只是另一个表中的 game_id，即 memory_games_game_state

出了什么问题？

更新:

我的 Django 模型:

class Game(models.Model):

    #Field for storing game type
    GAME_TYPE_CHOICES = (
        (8, 'Simon Game'),
        (9, 'Pinpoint reaction'),
        (10, 'Loslassen'),
        (11, 'Word pair'),
        (12, 'Wortschatz')
    )
    game_type = models.IntegerField(choices=GAME_TYPE_CHOICES)   
    level = models.IntegerField(default='1')
    #This helps to print in admin interface
    def __str__(self):
        return u"%s  level %s" % (self.get_game_type_display(), self.level)



class Game_state(models.Model):
    game = models.ForeignKey(Game, blank=True, null=True)
    user = models.ForeignKey(User, blank=True, null=True)
    level = models.IntegerField(default='1')
    progress = models.IntegerField(default='0')
    current_date = models.DateField(auto_now=True)

class Game_state_ui_model(models.Model):
    GAME_TYPE_CHOICES = (
        (8, 'Simon Game'),
        (9, 'Pinpoint reaction'),
        (10, 'Loslassen'),
        (11, 'Word pair'),
        (12, 'Wortschatz')
        )
    game_type = models.IntegerField(choices=GAME_TYPE_CHOICES)
    level = models.IntegerField()
    user_id = models.IntegerField(default='0')
    current_date = models.DateField(auto_now=True)

    # static method to fetch games for a paricular user  
    @staticmethod  
    def fetch_games(user_id, game_id):
        print("Fetching games in model")

        # create a cursor  
        cur = connection.cursor()  
        # execute the stored procedure passing in   
        # search_string as a parameter  
        cur.callproc('get_game_by_user_id', [user_id, game_id,])  
        # grab the results  
        results = cur.fetchall()

        cur.close()  

        Game_state_list=[] 

        for row in results:
            print("Get game", row)
            Gs = Game_state_ui_model()

            Gs.level=row[0]
            Gs.game_type=row[1]
            Gs.current_date=row[2]


            Game_state_list.append(Gs)

        return Game_state_list

Answer 1

作为错误状态，game_id 引用了 memory_games_game.id；不是 memory_games_game.game_type。

认为“game_type 12 只不过是另一个表中的 game_id，即 memory_games_game_state” 的想法是不正确的。

您需要在 memory_games_game 中有一行 id = 12。