I need a query (INFORMATION_SCHEMA) which will for given schema and table name show me all table columns with following attributes (what key type it is: PK=>Primary Key, UQ=>Unique Key, FK=>Foreign Key, what is key name, and if it is Foreign Key what schema.table.column is referenced):
COLUMN_NAME | DATA_TYPE | KEY_TYPE | KEY_NAME | REFERENCED
============+===========+==========+=============+========================
empid | int | PK | PRIMARY |
empname | varchar | UQ | uq_empname |
empactive | enum | | |
empcatid | int | FK | fk_emp_cat | schema.categories.catid
This is my SQL:
SELECT c.COLUMN_NAME,
c.COLUMN_KEY,
c.DATA_TYPE,
k.REFERENCED_TABLE_SCHEMA,
k.REFERENCED_TABLE_NAME,
k.REFERENCED_COLUMN_NAME
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_SCHEMA='PHPDAO'
AND c.TABLE_NAME='employees';
Here are my TABLES:
-- -----------------------------------------------------
-- Table `PHPDAO`.`categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PHPDAO`.`categories` (
`catid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`catname` VARCHAR(50) NOT NULL,
`catgroup` ENUM('A', 'B', 'C') NOT NULL DEFAULT 'A',
PRIMARY KEY (`catid`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `PHPDAO`.`employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PHPDAO`.`employees` (
`empid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`empname` VARCHAR(100) NOT NULL,
`empactive` ENUM('Y', 'N') NOT NULL DEFAULT 'Y',
`empcatid` INT UNSIGNED NOT NULL,
PRIMARY KEY (`empid`),
INDEX `fk_emp_cat_idx` (`empcatid` ASC),
UNIQUE INDEX `uq_empname` (`empname` ASC),
UNIQUE INDEX `uq_empid` (`empid` ASC),
CONSTRAINT `fk_emp_cat`
FOREIGN KEY (`empcatid`)
REFERENCES `PHPDAO`.`categories` (`catid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
This is my last (pivotized) query:
But I think there has to be simpler solution.