Question

我想提出这样的结论:

column_name, constraint_name, constraint_type, referenced_table_name, referenced_column_name

我试图这样做。

SELECT col.column_name, cons.constraint_name, cons.constraint_type,col.referenced_table_name, col.referenced_column_name
FROM information_schema.table_constraints cons, information_schema.key_column_usage col
WHERE col.table_name =  countries 
AND cons.constraint_name = col.constraint_name
AND cons.table_name = col.table_name

但是,我错过一栏——名称......:

请帮助我

Answer 1

SELECT `COLUMN_NAME`, `CONSTRAINT_NAME`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `TABLE_NAME` =  countries 
AND `CONSTRAINT_NAME` <>  PRIMARY ;

我不知道<代码>constraint_type 。我没有在信息栏中找到这一栏。

Answer 2

主要关键人物总是有名词,外国关键人物总是提到——名号——这样,你就可以从一个桌子——<代码>key_column_usage上获取:

SELECT
  column_name,
  constraint_name,
  CASE
    WHEN constraint_name =  PRIMARY  THEN  PRIMARY KEY 
    WHEN referenced_table_name IS NULL THEN  UNIQUE KEY 
    ELSE  FOREIGN KEY 
  END constraint_type,
  referenced_table_name,
  referenced_column_name
FROM
  information_schema.key_column_usage;

友情链接