Как узнать, является ли CONSTRAINT_NAME первичным или внешним ключом?

Question

Использование этого SQL на SQL Server 2005

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = @TableName
  AND COLUMN_NAME=@ColumnName

Я получаю первичные ключи и иностранные ключи.

Как я могу получить только иностранные ключи? Как узнать, является ли ограничение основным или внешним ключом?

Спасибо

Answer 1

Я использовал следующий SQL-код с SQL Server 2005 для получения имен ограничений по первичному ключу:

SELECT Constraint_Name 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE UNIQUE_CONSTRAINT_NAME = 
   (SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)

Чтобы получить имя ограничения с помощью Foreing Key:

SELECT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName

Чтобы получить таблицу и поле внешнего ключа по имени ограничения:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_NAME = @ConstraintName

Чтобы получить таблицу и поле первичного ключа по имени ограничения:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_NAME = 
    (SELECT UNIQUE_CONSTRAINT_NAME 
     FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
     WHERE CONSTRAINT_NAME = @ConstraintName)

Answer 2

Нашел гораздо более элегантное решение здесь

Код добавлен ниже для полноты, но весь кредит переходит к Пинал Дэйв

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, 
   fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, 
   fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
LEFT JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id

И если вы просто хотите первичные ключи...

SELECT i.name AS IndexName,
        OBJECT_NAME(ic.OBJECT_ID) AS TableName,
        COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
        INNER JOIN sys.index_columns AS ic
        ON i.OBJECT_ID = ic.OBJECT_ID 
    AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Добавил это как дополнительный ответ, потому что он так далек от моего предыдущего:)

Answer 3

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @TableName
  AND COLUMN_NAME=@ColumnName
  AND REFERENCED_COLUMN_NAME IS NOT NULL

Answer 4

Я использую что-то подобное. Это работает и в SQL Server 2000.

select  object_name(fkx.constid) as fk_name,
        fkx.keyno as num,
        object_name(fkx.fkeyid) as child_table,
        col_name(fkx.fkeyid, fkx.fkey) as child_column,
        object_name(fkx.rkeyid) as parent_table,
        col_name(fkx.rkeyid, fkx.rkey) as parent_column
from sysforeignkeys fkx
order by fk_name, fkx.keyno

Это все в системных таблицах и представлениях, которые довольно хорошо документированы.

Answer 5

Это не самое элегантное из решений, но MS SQL Server использует соглашение по именованию префиксов PK & FK для ключей, чтобы вы могли использовать что-то вроде...

SELECT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = @TableName
AND LEFT(CONSTRAINT_NAME,2) = 'FK'

Если это ваша СУБД:)

РЕДАКТИРОВАТЬ

Когда я попробовал ответить Quassnoi, я получил "Неверное имя столбца 'REFERENCED_COLUMN_NAME'". ошибка.

Answer 6

Это зависит от вашей СУБД, но ограничение должно иметь целевую таблицу и столбец где-то.

Если это ограничение внешнего ключа, таблица назначения будет отличаться от текущей таблицы. Пытаться

select *

вместо

select constraint_name

чтобы увидеть все магические колонны, с которыми вы можете работать.