0

Should the second ALTER TABLE CHECK CONSTRAINT be in an IF (NOT) EXISTS so that the script can be executed repeatedly?

IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME ='fk_RoleId'
)
BEGIN
    ALTER TABLE [dbo].[webpages_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [fk_RoleId] FOREIGN KEY([RoleId])
    REFERENCES [dbo].[webpages_Roles] ([RoleId])
END
GO

-- Put me in an IF ... () BEGIN ... END?
ALTER TABLE [dbo].[webpages_UsersInRoles] CHECK CONSTRAINT [fk_RoleId]
GO

It doesn't cause any errors, but if it should be in an IF clause, how do you test if it has been ran (what can the if contain)?

lko
  • 8,161
  • 9
  • 45
  • 62
  • The second step is redundant. See http://stackoverflow.com/questions/529941/whats-the-difference-between-with-check-add-constraint-followed-by-check-constr – Sean Oct 02 '13 at 13:12

1 Answers1

1

If you do want a check (it's not necessary), sys.foreign_keys has a column is_not_trusted:

FOREIGN KEY constraint has not been verified by the system.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448