gotchasqlMinor
Why does Management Studio add the second alter table "CHECK CONSTRAINT" for a foreign key constraint
Viewed 0 times
whythekeyconstraintforeignseconddoesstudiomanagementfor
Problem
ALTER TABLE [dbo].[REMINDER_EMAIL] WITH CHECK ADD CONSTRAINT
[FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] )
REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] )
ALTER TABLE [dbo].[REMINDER_EMAIL] CHECK CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID]Solution
With foreign key and check constraints, specifying
{ CHECK | NOCHECK } CONSTRAINT
Specifies that constraint_name is
enabled or disabled. This option can only be used with FOREIGN KEY and
CHECK constraints. When NOCHECK is specified, the constraint is
disabled and future inserts or updates to the column are not validated
against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
constraints cannot be disabled.
Reference: BOL ALTER TABLE
It is redundant and unnecessary, but I just take it as "verbose". If I was to defend it forcefully, I'd say it leaves the script reader without any question of functionality.
CHECK or NOCHECK enables or disables it. It's just explicit DDL that is generated by the GUI that, in your case, enables the foreign key constraint:{ CHECK | NOCHECK } CONSTRAINT
Specifies that constraint_name is
enabled or disabled. This option can only be used with FOREIGN KEY and
CHECK constraints. When NOCHECK is specified, the constraint is
disabled and future inserts or updates to the column are not validated
against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
constraints cannot be disabled.
Reference: BOL ALTER TABLE
It is redundant and unnecessary, but I just take it as "verbose". If I was to defend it forcefully, I'd say it leaves the script reader without any question of functionality.
Context
StackExchange Database Administrators Q#24824, answer score: 5
Revisions (0)
No revisions yet.