信息模式和主键 [英] Information schema and Primary Keys

问题描述

如何使用主键为列打印出主键？

How do I just print out a 'primary key' for the column with the primary key?

如果表具有主键，而不是具有主键的一列，并且键类型中的其他列为空，则我为所有列获取主键 。

I get 'primary key' for all the columns if the table has a primary key, instead of the one column with the primary key and the other columns as blank in keyType.

   SELECT c.TABLE_NAME, 
          c.COLUMN_NAME, 
          c.DATA_TYPE, 
          c.Column_default, 
          c.character_maximum_length, 
          c.numeric_precision, 
          c.is_nullable,
          CASE 
            WHEN u.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'primary key'
            ELSE '' 
          END AS KeyType
     FROM INFORMATION_SCHEMA.COLUMNS as c
LEFT JOIN information_schema.table_constraints as u ON c.table_name = u.table_name
 ORDER BY table_name

推荐答案

SELECT  c.TABLE_NAME, c.COLUMN_NAME,c.DATA_TYPE, c.Column_default, c.character_maximum_length, c.numeric_precision, c.is_nullable
             ,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   pk 
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME
ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME, c.ORDINAL_POSITION 

这篇关于信息模式和主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！