获取有关约束的完整信息

发布于 2024-12-26 13:35:09 字数 561 浏览 5 评论 0原文

我想创建结果如下的查询：

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

，但是我得到了错误的column_name...:(

请帮助我...

原文

I want to create query that result like this :

column_name, constraint_name, constraint_type, referenced_table_name, referenced_column_name

I have try something like this

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

but, I get wrong column_name... :(

please help me...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡淡绿茶香 2025-01-02 13:35:09

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。我在 information_schema 中没有找到此列。

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';

I don't know about constraint_type. I haven't found this column in information_schema.

回复收藏 0 原文

蔚蓝源自深海 2025-01-02 13:35:09

主键的名称始终为“PRIMARY”，外键的名称始终为referenced_table_name，因此您可以从一个表中获取该信息 - 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;

Primary keys always have name 'PRIMARY', foreign keys always have referenced_table_name, so you can get that info from one table - 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;

回复收藏 0 原文

~没有更多了~