1

I am working on SDL Server 2008 R2, where I generated a schema-only database script. The generated script is as follows:

ALTER TABLE [dbo].[ConsoleServer]  WITH CHECK ADD  CONSTRAINT [FK_ConsoleServer_RackUnits] FOREIGN KEY([RackUnitID])
REFERENCES [dbo].[RackUnits] ([UnitID])
GO
ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]

I have these 2 questions:-

  • I know that the first line is responsible to create a FK between two DB tables. but what is the purpose of the following :

    ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]

  • In general, why does the DB script have the word GO. Now if I remove it the script will be executed well on the destination DB, so why it is included in the script prior to any statement?

Cartier
  • 429
  • 4
  • 15
John John
  • 1
  • 72
  • 238
  • 501
  • Your first question is answered [here](http://stackoverflow.com/a/530303/2091410). As for the `GO`, it's answered well [here](http://stackoverflow.com/a/20711373/2091410). The GO statement isn't needed for your situation. It's just a case of SSMS being extra careful when emitting the SQL commands because it doesn't know what the *next* command will be. – Ed Gibbs Jul 04 '15 at 02:24
  • @EdGibbs and what about the "ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]" ? do i need it ,, since i am creating a constraint with Check in the later table ? – John John Jul 04 '15 at 19:44
  • I don't think you do. It would make sense if the constraint was initially defined `WITH NOCHECK`, but it wasn't. Just another case I think of SSMS being overly cautious. – Ed Gibbs Jul 05 '15 at 03:14

1 Answers1

3

  1. The ALTER TABLE ... CHECK CONSTRAINT ... line enables the constraint. You can add a constraint and leave it disable (while you clean up the data for example). See more here

  2. GO is a batch separator, it's only recognized by SSMS. Some statements, such as CREATE PROCEDURE... requires it to be the first statement in the batch. You can type it out in a new file, or use GO to terminate the previous batch. Don't send GO from your application through OLEDB or ADO.NET though.

Community
  • 1
  • 1
Code Different
  • 90,614
  • 16
  • 144
  • 163
  • so in my case as i specify to create the constraint "with check" in the alter statment,,, do i still need the second statment ? – John John Jul 04 '15 at 19:40
  • so why sql server management studio specify this when i generate a schema only script for the table ? is there a reason for this ? – John John Jul 04 '15 at 20:09
  • 1
    I guess you use the Create Script feature from the contextual menu. SSMS **always** does that. You would also notice that it puts everything in square brackets, like `CREATE TABLE [dbo].[my_table]`. Microsoft chose to do it that way. – Code Different Jul 04 '15 at 20:16