如何检查是否列已经是一个外键？ [英] How to check if a column is already a foreign key?

问题描述

我有一个名为Person表和一个名为列ID
如何检查ID已FOREIGN KEY，因为我想和这个代码，使其：

I have table named Person and column named ID how to check if ID is already FOREIGN KEY cause I want to make it with this code:

ALTER TABLE Person ADD FOREIGN KEY(ID) REFERENCES Job(ID)
ON DELETE CASCADE ON UPDATE CASCADE

但如果ID已经是一个外键它给了我下面的错误可能会导致循环或多重级联路径，因为有两个级联的条件...如何检查，如果这字段外键避免这个错误？

but if ID is already a FOREIGN KEY it gives me the following error "may cause cycles or multiple cascade paths" because of the condition with two cascades... How to check if this field is FOREIGN KEY to avoid this error?

推荐答案

您会希望在信息模式看的意见

虽然它并不完整，因为它应该是。这是你想要的最终查询：

Though it's not as complete as it should be. This is the final query you'd want:

SELECT 
     KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
   , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
   , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
   , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
   , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
   , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
   , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
   , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG 
   AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
   AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG = 
RC.UNIQUE_CONSTRAINT_CATALOG 
   AND KCU2.CONSTRAINT_SCHEMA = 
RC.UNIQUE_CONSTRAINT_SCHEMA
   AND KCU2.CONSTRAINT_NAME = 
RC.UNIQUE_CONSTRAINT_NAME
   AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION

在这里看到更多信息。

http://msdn.microsoft.com/ EN-US /库/ aa175805（v = sql.80）的.aspx

这篇关于如何检查是否列已经是一个外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！