MySQL column name with singl UNIQUE constraint indicator

Question

I need a query which would (for given table name) show a list of columns with indicator if there exists UNIQUE constraint for this column BUT only if this column is SINGLE COLUMN constraint, NOT if this column is part of multiple column constraint.

For example for this table:

CREATE TABLE IF NOT EXISTS `prices` (
  `priceId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `motorcycleId` INT UNSIGNED NOT NULL,
  `priceDatum` DATE NOT NULL,
  `price` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`priceId`),
  INDEX `price_motorcycleId_fk_idx` (`motorcycleId` ASC),
  UNIQUE INDEX `priceId_UNIQUE` (`priceId` ASC),
  UNIQUE INDEX `price_UNIQUE` (`motorcycleId` ASC, `priceDatum` ASC),
  CONSTRAINT `price_motorcycleId_fk`
    FOREIGN KEY (`motorcycleId`)
    REFERENCES `motorcycles` (`motorcycleId`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

this query should give:

COLUMN      |UNIQUE
============+======
priceId     |  Y
motorcycleId|  N    <-- should not be shown as UNIQUE since not single constraint
priceDatum  |  N    <-- should not be shown as UNIQUE since not single constraint
price       |  N

Answer 1

The solution is:

SELECT c.COLUMN_NAME,
        IF((SELECT COUNT(k2.COLUMN_NAME)
          FROM information_schema.KEY_COLUMN_USAGE k1
          JOIN information_schema.KEY_COLUMN_USAGE k2 
               ON (k1.CONSTRAINT_NAME=k2.CONSTRAINT_NAME
               AND k1.TABLE_NAME=k2.TABLE_NAME
               AND k1.TABLE_SCHEMA=k2.TABLE_SCHEMA)
          JOIN information_schema.TABLE_CONSTRAINTS tc
               ON (k2.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
               AND k2.TABLE_NAME=tc.TABLE_NAME
               AND k2.TABLE_SCHEMA=tc.TABLE_SCHEMA
               AND tc.CONSTRAINT_TYPE='UNIQUE')
         WHERE k1.COLUMN_NAME=c.COLUMN_NAME
           AND k1.TABLE_NAME=c.TABLE_NAME)=1,'Y','N') AS `UNIQUE`
  FROM information_schema.COLUMNS c
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k
   ON (k.TABLE_SCHEMA=c.TABLE_SCHEMA
       AND k.TABLE_NAME=c.TABLE_NAME
       AND k.COLUMN_NAME=c.COLUMN_NAME
       AND k.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL)
 WHERE c.TABLE_NAME='prices'
 ORDER BY c.ORDINAL_POSITION