我有名为 Person 的表和名为 ID 的列
如何检查 ID 是否已经是外键,因为我想用以下代码来实现:
ALTER TABLE Person ADD FOREIGN KEY(ID) REFERENCES Job(ID)
ON DELETE CASCADE ON UPDATE CASCADE
但如果 ID 已经是外键,它会给我以下错误“可能导致循环或多个级联路径”,因为有两个级联的条件...如何检查该字段是否是外键以避免此错误?
你想看看INFORMATION SCHEMA
views
尽管它并不像应有的那么完整。这是您想要的最终查询:
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/library/aa175805(v=sql.80).aspx http://msdn.microsoft.com/en-us/library/aa175805(v=sql.80).aspx
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)