SQL 2000 sys.foreign_keys无效错误(sql2000 添加外键判断)
如果需要判断某个表是否存在外键,然后决定是否添加该外键的话,sql 2005可以这样写(以用户和权限两个表为例,给用户表添加一个权限的外键)
if not exists(select name from sys.foreign_keys where parent_object_id=object_id(N'[dbo].[User]') and referenced_object_id=object_id(N'[dbo].[Role]')
)
BEGIN
ALTER TABLE [dbo].[User] ADD
FOREIGN KEY
(
[Role_ID]
) REFERENCES [dbo].[Role] (
[ID]
)
END
GO
)
BEGIN
ALTER TABLE [dbo].[User] ADD
FOREIGN KEY
(
[Role_ID]
) REFERENCES [dbo].[Role] (
[ID]
)
END
GO
if not exists(select constid from sysforeignkeys where fkeyid=object_id(N'[dbo].[User]') and rkeyid=object_id(N'[dbo].[Role]')
由于sql server语句基本是向下兼容的,所以可以说下边语句是通用语句(2008中验证通过)