来自information_schema的约束详细信息（在更新级联上，在删除限制上） [英] Constraint detail from information_schema (on update cascade, on delete restrict)

问题描述

几乎我需要一个数据库的所有信息，我可以在information_schema中找到

这次我需要阅读数据库通过单个查询我发现在information_schema.key_Column_usage的每一件事，但没有找到约束像删除，更新

我可以为所有单个表做 show create table 。

  SELECT CONSTRAINT_NAME，TABLE_NAME，COLUMN_NAME，REFERENCED_TABLE_NAME，
 REFERENCED_COLUMN_NAME FROM information_schema.`KEY_COLUMN_USAGE` WHERE 
 table_schema ='mydbname'AND referenced_column_name IS NOT NULL 
  

它是做的工作，但只是缺少像删除上的约束，在更新如何获得这些值，以便我可以得到所有的信息外国

UPDATE_RULE 和<$ c $

c> DELETE_RULE 是你要求的

这有点太晚了，但它可以帮助别人，这里的解决方案： p>

 选择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 ='sfa'AND referenced_column_name IS NOT NULL 
  

Almost all the information I had needed about a database, I could find in information_schema

This time I needed to read details of all foreign keys in a database through single query I found every thing in information_schema.key_Column_usage but could not find the constraints like on delete, on update

I could do show create table for all individual tables. But is there any way to get these details through some select query like this?

SELECT CONSTRAINT_NAME, TABLE_NAME,COLUMN_NAME, REFERENCED_TABLE_NAME, 
REFERENCED_COLUMN_NAME FROM information_schema.`KEY_COLUMN_USAGE` WHERE 
table_schema = 'mydbname' AND referenced_column_name IS NOT NULL

It is doing the job well but just missing constraints like on delete, on update How can I get those values as well so that I can get all info about foreign keys in a single query?

解决方案

UPDATE_RULE and DELETE_RULE is the thing you asked for

it's a little bit too late but it could help someone else, here the solution :

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 = 'sfa' AND referenced_column_name IS NOT NULL

这篇关于来自information_schema的约束详细信息（在更新级联上，在删除限制上）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！