Имя столбца MySQL с единственным индикатором ограничения UNIQUE

Мне нужен запрос, который (для заданного имени таблицы) покажет список столбцов с индикатором, если для этого столбца существует ограничение UNIQUE, НО только если этот столбец является ограничением SINGLE COLUMN, а НЕ, если этот столбец является частью ограничения нескольких столбцов.

Например для этой таблицы:

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;

этот запрос должен дать:

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

mysql information-schema
person sbrbot    schedule 12.12.2017    source источник
comment
Примечание: уникальное ограничение является избыточным для priceId, поскольку первичный ключ уникален по определению.   -  person Michael - sqlbot    schedule 13.12.2017
comment
Спасибо @Michael-sqlbot за вашу заметку, это ценно. Я знаю, что это избыточно, но я поместил это здесь только ради примера. Недавно я обсуждал это (посмотрите stackoverflow. ком/вопросы/47493487/)   -  person sbrbot    schedule 13.12.2017

Ответы (2)


arrow_upward
0
arrow_downward

Вы должны не только проверить, существует ли такое ограничение, но и проверить, имеет ли это ограничение только один столбец. Таким образом, счетчик для этого ограничения в KEY_COLUMN_USAGE должен быть равен 1.

Я не могу проверить это в данный момент, но это должно выглядеть примерно так, чтобы получить столбцы с таким ограничением. Вы можете сделать то же самое в подзапросе или просто оставить этот запрос в списке всех столбцов и проверить: если запись существует, существует уникальное ограничение для одного столбца, в противном случае его нет.

select
  k.COLUMN_NAME 
from 
  information_schema.KEY_COLUMN_USAGE k
  join information_schema.TABLE_CONSTRAINTS tc
    on k.TABLE_SCHEMA = tc.TABLE_SCHEMA
      and k.TABLE_NAME = tc.TABLE_NAME
      and k.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
where
  k.TABLE_NAME = 'prices'
  and tc.CONSTRAINT_TYPE = 'UNIQUE'
group by
  k.CONSTRAINT_CATALOG,
  k.CONSTRAINT_SCHEMA,
  k.CONSTRAINT_NAME
having
  count(*) = 1
person GolezTrol    schedule 12.12.2017
comment
Спасибо @GolezTrol за ваши усилия. Я пробовал почти то же самое с HAVING COUNT(*)=1, но безуспешно. Ваш подзапрос не работает. Вот SQLFiddle sqlfiddle.com/#!9/cbe483/17, как я это сделал это, но я не уверен, что это лучший подход. Все еще думаю, что это можно сделать с помощью GROUP BY HAVING и сокращения внутреннего SELECT для одного JOIN. - person sbrbot; 13.12.2017

arrow_upward
0
arrow_downward

Решение:

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
person sbrbot    schedule 18.12.2017