sql_foreign_keys doesn't guarantee column order

[GoogleCodeExporter]

revision 68

problem: sql_foreign_keys

The group_concat expressions for the referencing and referenced columns do not 
include an ORDER BY clause. They should in order to guarantee column order for 
multi-column foreign keys. With the current implementation of the I_S it's not 
likely to go wrong, but to be sure of the correct output, ORDER BY clauses 
should be added. 

Modified view code below:

CREATE OR REPLACE
ALGORITHM = UNDEFINED
SQL SECURITY INVOKER
VIEW sql_foreign_keys AS
  SELECT 
    KEY_COLUMN_USAGE.TABLE_SCHEMA,
    KEY_COLUMN_USAGE.TABLE_NAME,
    KEY_COLUMN_USAGE.CONSTRAINT_NAME,
    CONCAT(
      'ALTER TABLE `', KEY_COLUMN_USAGE.TABLE_SCHEMA, '`.`', KEY_COLUMN_USAGE.TABLE_NAME, 
      '` DROP FOREIGN KEY `', KEY_COLUMN_USAGE.CONSTRAINT_NAME, '`'
    ) AS drop_statement,
    CONCAT(
      'ALTER TABLE `', KEY_COLUMN_USAGE.TABLE_SCHEMA, '`.`', KEY_COLUMN_USAGE.TABLE_NAME, 
      '` ADD CONSTRAINT `', KEY_COLUMN_USAGE.CONSTRAINT_NAME, 
      '` FOREIGN KEY (', GROUP_CONCAT('`', KEY_COLUMN_USAGE.COLUMN_NAME, '`' ORDER BY KEY_COLUMN_USAGE.ORDINAL_POSITION), ')', 
      ' REFERENCES `', KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, '`.`', KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, 
      '` (', GROUP_CONCAT('`', KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, '`' ORDER BY KEY_COLUMN_USAGE.ORDINAL_POSITION), ')',
      ' ON DELETE ', MIN(REFERENTIAL_CONSTRAINTS.DELETE_RULE), 
      ' ON UPDATE ', MIN(REFERENTIAL_CONSTRAINTS.UPDATE_RULE)
    ) AS create_statement
  FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS USING(CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
  WHERE 
    KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA IS NOT NULL
  GROUP BY
    KEY_COLUMN_USAGE.TABLE_SCHEMA, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.CONSTRAINT_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME
;

(Also, the superfluous CONCAT inside the GROUP_CONCAT was removed)

Original issue reported on code.google.com by roland.bouman on 6 Sep 2011 at 1:15

[GoogleCodeExporter]

This issue was closed by revision r70.

Original comment by shlomi.n...@gmail.com on 7 Sep 2011 at 5:24

• Changed state: Fixed

[GoogleCodeExporter]

I actually never was aware you can put as many arguments in GROUP_CONCAT and 
have them concatenated. And I've stared at the GROUP_CONCAT definition for many 
times :)

Fixed in revision 70 (not committed yet).

Original comment by shlomi.n...@gmail.com on 7 Sep 2011 at 5:25