8

I am creating a class which, takes a table from a database, and displays it to a web-page, with as much functionality as possible. One of the things I would like to support, would be having the class detect which columns in the table have a foreign key constraint on them, so that it can then go to those tables, get all of their values and use them in a select-box which is called when you edit those fields, to avoid someone violating foreign key constraints,

The main problem is discovering which fields have a foreign key constraint on them, and which tables they are pointing to. Does anyone know how to do this???

Thanks,

Lemiant

lemiant
  • 4,205
  • 4
  • 31
  • 38
  • I asked this same question a while back--see http://stackoverflow.com/questions/273794/mysql-how-to-determine-foreign-key-relationships-programmatically – Drew Hall Nov 11 '10 at 23:04

4 Answers4

15

Simple way to get foreign keys for given table:

SELECT
    `column_name`, 
    `referenced_table_schema` AS foreign_db, 
    `referenced_table_name` AS foreign_table, 
    `referenced_column_name`  AS foreign_column 
FROM
    `information_schema`.`KEY_COLUMN_USAGE`
WHERE
    `constraint_schema` = SCHEMA()
AND
    `table_name` = 'your-table-name-here'
AND
    `referenced_column_name` IS NOT NULL
ORDER BY
    `column_name`;
dev-null-dweller
  • 29,274
  • 3
  • 65
  • 85
  • `table_schema = DATABASE()` for the currently connected DB. – Xeoncross Aug 02 '13 at 17:44
  • I don't get the `constraint_schema` = SCHEMA() part, constraint_schema is just the name of the constraint, am I right? It should be table_schema = 'your-db-name-here' instead – Eugenio Apr 19 '16 at 07:40
5

The INFORMATION_SCHEMA database contains details of the full schema of all other databases, including constraints:

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

You can also run a SHOW CREATE TABLE query to get the SQL to create a table, including its constraints.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • 1
    To add, FK info is only available on tables that use InnoDB storage. From the manual:`For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.` – webbiedave Nov 11 '10 at 22:54
  • thanks, `SHOW CREATE TABLE tableName` shows all needed informations! – user2342558 Nov 04 '20 at 11:31
0

Much can be retrieved from MySQL's information_schema, foreign keys included, as pointed out by dev-null-dweller.

1

SELECT * FROM information_schema.table_constraints 
         WHERE table_schema = 'dbname' AND table_name='mytable';

Instead of dbname use the function SCHEMA() to set the name of the database in USE.

2

As pointed out by Dan Grossman, the command

SHOW CREATE TABLE `yourtablename`

can be used basically get an SQL dump of the create table statement.

~3

MySQL provides a SHOW KEYS command. As such you could theoretically get the FK if you know a lower cardinality threshold and have few other keys in the table.

SHOW KEYS FROM `address` WHERE Non_unique AND CARDINALITY > 10000

As the key's cardinality changes each time the internal database is changed, this is rather theoretical. See the cardinality change for instance with running ANALYZE TABLE.

~4

It is useful to stick to a naming schema, such as foreigntablename_foreignfieldname. For example the field user_id in a table billing. Several ORMs of big Web Content Frameworks use this schema.

Community
  • 1
  • 1
Lorenz Lo Sauer
  • 23,698
  • 16
  • 85
  • 87
0

based on Bill Karwin answer in this other thread, I used this solution to get all the info I needed, included on_delete and on_update rules:

SELECT kcu.referenced_table_schema, kcu.constraint_name, kcu.table_name, kcu.column_name, kcu.referenced_table_name, kcu.referenced_column_name, 
   rc.update_rule, rc.delete_rule 
FROM INFORMATION_SCHEMA.key_column_usage kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on kcu.constraint_name = rc.constraint_name
WHERE kcu.referenced_table_schema = 'db_name' 
AND kcu.referenced_table_name IS NOT NULL 
ORDER BY kcu.table_name, kcu.column_name
kinsay
  • 29
  • 4