Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 3

Displaying posts with tag: MySQL information schema (reset)

Simple Query to identify Foreign Key references on Deadlocked Tables
+2 Vote Up -0Vote Down

The other day, I was troubleshooting a deadlock, and I wondered if any of the table’s columns were referenced by any foreign keys (fks) from any other tables in the instance.

Well, this is actually very simple with information_schema (I_S):

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

Where `parent` is the name of the table you’re searching for.

Note this query does not restrict on the database, or schema, name, but that could easily be added (or any other number of conditions). Here is an example where I only return the most useful columns (which could be useful for determining said conditions):

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA,
	TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
	REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  [Read more...]
MySQL information_schema: Identifying rows from TABLE_CONSTRAINTS
+0 Vote Up -0Vote Down
Yesterday, I set out a little quiz about the TABLE_CONSTRAINTS table in the MySQL information_schema. The task was:
  • Specify a minimal set of columns of the information_schema.TABLE_CONSTRAINTS table that is sufficient to reliably identify a single row in the information_schema.TABLE_CONSTRAINTS table.
  • Argue why these columns are necessary and sufficient to identify a row, and why a smaller set of columns does not exist

Short Answer


For MySQL there are two such column sets:


      [Read more...]
    MySQL Information Schema applications at the UC2008
    +0 Vote Up -0Vote Down
    Last week I blogged about the upcoming MySQL Users conference, in particular about the Writing MySQL UDFs tutorial that I will be delivering.

    I will also be doing the Grand Tour of the Information Schema and its Applications.


    I will discuss the elements in the




      [Read more...]
    Showing entries 1 to 3

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.