CODEWITHSUNDEEP
mysqlsqlforeign-key-relationship
The Georgia
The Georgia

Reputation: 1075

Query to find foreign keys on database schema

I have two questions i need help with:

  1. I would like to have a query that displays foreign key relationships on tables in a schema. My whole goal is to find out if tables in a schema have foreign keys declared to establish relationships between the tables in the schema. For example, using "show crate table " will display the foreign keys on a table if declared by the developer. I tried to use "show table status on " and expected to see foreign key relationships in the comments column, but it was not the case. Anyway, is there a query i can use to check for foreign key relationships between tables in a schema?

  2. What are some of the good queries i can use to perform analysis on database? I have been given an old database that i need to analyze so we can improve on its design and structure.

Thanks.

Upvotes: 3

Views: 3593

Answers (1)

Alma Do
Alma Do

Reputation: 37365

You may use INFORMATION_SCHEMA for this:

SELECT 
  * 
FROM  
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE 
  CONSTRAINT_TYPE='FOREIGN KEY'

Possible types of constraint may be:

  • PRIMARY KEY for primary keys
  • FOREIGN KEY for foreign keys
  • UNIQUE for unique constraints

So you're interested in FOREIGN KEY type. This will show you which table on which column has the constraint, but won't show you targeted constraint column and table. To find them, you need to use another table, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS which has such information, so, basically, to reconstruct relation between tables, you'll need:

SELECT 
  t.TABLE_SCHEMA, 
  t.TABLE_NAME, 
  r.REFERENCED_TABLE_NAME 
FROM  
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t 
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS r 
    ON t.CONSTRAINT_NAME=r.CONSTRAINT_NAME 
WHERE 
  t.CONSTRAINT_TYPE='FOREIGN KEY'

But that's, again, is missing columns (because it doesn't belongs to those tables) and will show only relations via FK between tables. To reconstruct full relation (i.e. with columns involved) you'll need to refer to KEY_COLUMN_USAGE table:

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME, 
  COLUMN_NAME, 
  REFERENCED_TABLE_SCHEMA, 
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME 
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
  REFERENCED_TABLE_SCHEMA IS NOT NULL

This query will show all relations where referenced entity is not null, and, since it's applicable only in FK case - it's an answer to the question of finding FK relations. It's quite universal, but I've provided methods above since it may be useful to get info about PK or unique constraints too.

Upvotes: 4

Related Questions