Mapping columns of MYSQL table with key types and reference
I need a query (INFORMATION_SCHEMA) which, for a given schema and table name, will show me all the columns of the table with the following attributes (what type of key: PK => Primary key, UQ => Unique key, FK => Foreign key, what is the key name, and if it's a foreign key referenced by the schema.table.column address):
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;
+3
source to share
1 answer
This is my final (twist) request:
SELECT c.COLUMN_NAME,
--c.COLUMN_KEY,
IF(EXISTS(select *
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_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
AND c.COLUMN_NAME=k.COLUMN_NAME),'PK',null) AS PK,
IF(EXISTS(select *
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_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND tc.CONSTRAINT_TYPE='UNIQUE'
AND c.COLUMN_NAME=k.COLUMN_NAME),'UQ',null) AS UQ,
IF(EXISTS(select *
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_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND tc.CONSTRAINT_TYPE='FOREIGN KEY'
AND c.COLUMN_NAME=k.COLUMN_NAME),'FK',null) AS FK,
c.EXTRA,
c.IS_NULLABLE,
c.DATA_TYPE,
c.COLUMN_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.COLUMN_COMMENT,
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';
But I think there should be a simpler solution.
0
source to share