python - 如何检测mysql存储过程中是否添加了外键？

Question

我尝试了以下方法，但由于某种原因，@check 参数被设置为我正在尝试检查的约束值，即使该约束不存在也是如此。

有什么想法吗？

CREATE PROCEDURE AddForeignKey(
    IN constraint_name varchar(64),
    IN foreign_key_column_name varchar(64),
    IN table_name varchar(64),
    IN database_name varchar(64),
    IN foreign_table_key_name varchar(64),
    IN foreign_table_name varchar(64))

BEGIN
    set @sql = CONCAT('ALTER TABLE ', database_name, '.', table_name,
            ' ADD CONSTRAINT ', constraint_name,
                ' FOREIGN KEY (', foreign_key_column_name, ')'
                ' REFERENCES ', foreign_table_name, ' (', foreign_table_key_name, ');' ) ;

    set @dbname = database_name;

    set @fkname = constraint_name;

    set @check = '';

    SELECT 
        CONSTRAINT_NAME
    INTO
        @check
    FROM 
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE
       CONSTRAINT_SCHEMA = @dbname
    AND
       CONSTRAINT_NAME   = @fkname
    AND
       CONSTRAINT_TYPE   = 'FOREIGN KEY'
    LIMIT 1;

    IF @check != @fkname
    THEN
        prepare stmt from @sql;
        execute stmt;
    ELSE
        insert into errors(message) values (CONCAT ( 'Check: ', @check, ' DB:', @dbname, ' FK:', @fkname, ' Could not execute statement: ',  @sql));
    END IF;
END

Answer 1

您只比较不足以防止外键重复创建的列 CONSTRAINT_NAME。

您必须添加 REFERENTIAL_CONSTRAINTS 表并添加更多列以进行比较。

我还更改了变量名以避免不必要的变量名:

CREATE PROCEDURE AddForeignKey(
    IN p_constraint_name varchar(64),
    IN foreign_key_column_name varchar(64),
    IN p_table_name varchar(64),
    IN p_database_name varchar(64),
    IN foreign_table_key_name varchar(64),
    IN foreign_table_name varchar(64))

BEGIN
    set @sql = CONCAT('ALTER TABLE `', p_database_name, '`.`', p_table_name,
        '` ADD CONSTRAINT `', p_constraint_name,
            '` FOREIGN KEY (`', foreign_key_column_name, '`)'
            ' REFERENCES `', foreign_table_name, '` (`', foreign_table_key_name, '`);' ) ;

    set @check = '';

    SELECT 
        TABLE_CONSTRAINTS.CONSTRAINT_NAME
        INTO @check
    FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
            INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
            ON  TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA = REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
              AND TABLE_CONSTRAINTS.CONSTRAINT_NAME = REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
              AND TABLE_CONSTRAINTS.TABLE_NAME = REFERENTIAL_CONSTRAINTS.TABLE_NAME

    WHERE
    TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA =  p_database_name
        AND TABLE_CONSTRAINTS.CONSTRAINT_NAME   =  p_constraint_name
        AND TABLE_CONSTRAINTS.CONSTRAINT_TYPE   = 'FOREIGN KEY'
    AND TABLE_CONSTRAINTS.TABLE_NAME = p_table_name
        and REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAME = foreign_table_name
    LIMIT 1;

    IF @check <> p_constraint_name
    THEN
        prepare stmt from @sql;
        execute stmt;
    ELSE
        insert into errors(message) values (CONCAT ( 'Check: ', @check, ' DB:', @dbname, ' FK:', 
        @fkname, ' Could not execute statement: ',  @sql));
    END IF;

END