我已经尝试了以下几点,但是由于某种原因,即使不存在约束,@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

最佳答案

您只比较列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

关于python - 如何检测外键是否已添加到mysql存储过程中?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43573049/

10-10 23:29
查看更多