Foreign key flaws missing in SHOW CREATE TABLE output

Question

I find that SHOW CREATE TABLE does not show foreign key constraints as I expected.

To demonstrate, here is an example from the MySQL manual:

 CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB; mysql> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE "child" ( "id" int(11) default NULL, "parent_id" int(11) default NULL, KEY "par_ind" ("parent_id") ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 

In this release, I expected to see something like:

 CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE 

in that it creates the output of the table but clearly does not exist.

However, there is a limitation:

 mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema=database()\G *************************** 1. row *************************** CONSTRAINT_CATALOG: NULL CONSTRAINT_SCHEMA: test_fk CONSTRAINT_NAME: child_ibfk_1 TABLE_CATALOG: NULL TABLE_SCHEMA: test_fk TABLE_NAME: child COLUMN_NAME: parent_id ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: test_fk REFERENCED_TABLE_NAME: parent REFERENCED_COLUMN_NAME: id *************************** 2. row *************************** CONSTRAINT_CATALOG: NULL CONSTRAINT_SCHEMA: test_fk CONSTRAINT_NAME: PRIMARY TABLE_CATALOG: NULL TABLE_SCHEMA: test_fk TABLE_NAME: parent COLUMN_NAME: id ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: NULL REFERENCED_TABLE_SCHEMA: NULL REFERENCED_TABLE_NAME: NULL REFERENCED_COLUMN_NAME: NULL 2 rows in set (0.01 sec) mysql> SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_schema=database()\G *************************** 1. row *************************** CONSTRAINT_CATALOG: NULL CONSTRAINT_SCHEMA: test_fk CONSTRAINT_NAME: child_ibfk_1 TABLE_SCHEMA: test_fk TABLE_NAME: child CONSTRAINT_TYPE: FOREIGN KEY *************************** 2. row *************************** CONSTRAINT_CATALOG: NULL CONSTRAINT_SCHEMA: test_fk CONSTRAINT_NAME: PRIMARY TABLE_SCHEMA: test_fk TABLE_NAME: parent CONSTRAINT_TYPE: PRIMARY KEY 2 rows in set (0.01 sec) 

I get the same results with examples of my own design.

Any idea what is going on?

---

UPDATE 2011-01-08 . I think this has something to do with the sql_mode variable. But at the moment I do not know which mode setting excludes restrictions from the output of SHOW CREATE TABLE.

Answer 1

Setting the mysql sql_mode variable was definitely the reason I reported the behavior. I deleted the line that installed it from my.cnf and mysql performed the expected behavior. But I forgot what this setting is, and since then I have not understood. If someone can determine that, go straight ahead.

Answer 2

Unfortunately, I can not comment on how I wanted, so I hear a weak answer to your question.,.

This team is working! when creating a table, try to implement a foreign key with: constraint and name / id!

CONSTRAINT 'child_fk1' FOREIGN KEY ('parent_id') LINKS parent ('id')

may help, otherwise check the oracle mysql syntax online, responding to your server version !!

http://dev.mysql.com/doc/refman/5.0/en/create-table.html reviews

Answer 3

For those who find this, this query will help:

 SELECT tb1.CONSTRAINT_NAME, tb1.TABLE_NAME, tb1.COLUMN_NAME, tb1.REFERENCED_TABLE_NAME, tb1.REFERENCED_COLUMN_NAME, tb2.MATCH_OPTION, tb2.UPDATE_RULE, tb2.DELETE_RULE FROM information_schema.`KEY_COLUMN_USAGE` AS tb1 INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS tb2 ON tb1.CONSTRAINT_NAME = tb2.CONSTRAINT_NAME WHERE table_schema = '{DATABASE_NAME}' AND referenced_column_name IS NOT NULL 

Originally taken from: here

Unfortunately, changing sql_mode to my.cnf did not affect the output:

 SHOW CREATE TABLE {TABLE_NAME}; 

He still did not show the "ON DELETE" constraint. Used MySQL version 5.6.22 on Mac OS X 10.9.