locked
Creating Correct Scripts RRS feed

  • Question


  • Hi all,

    I have this table with 2 columns called x1 and x2. This table needs to be altered by adding column x3 and x4.
    x4 is a primary key, so also an add constraint must be added.

    I'm providing smo with the column and index data. the script I'm returning is  the following ;

    USE [sc1]
    ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
    (
        [x4]
    )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
    USE [sc1]
    ALTER TABLE [dbo].[Table_1] ADD [x3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Table_1_x3]  DEFAULT test
    ALTER TABLE [dbo].[Table_1] ADD [x4] [int] IDENTITY(1,1)
     

    This script when executed will not work as the add constraint will fail as x4 is not added yet to the table1.

    I cant manage to create a script that  will first create the column and then the constraint.


    If anyone knows how please enlighten me  !


    Thanks

    Matt


    Friday, January 18, 2008 12:45 PM

Answers

  • Hello,

     

    You have to do the ADD [x4] before adding constraint on x4

     

    USE [sc1]

    GO

    ALTER TABLE [dbo].[Table_1] ADD [x4] [int] IDENTITY(1,1)
    GO
    ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
    (
        [x4]
    )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

    GO
    ALTER TABLE [dbo].[Table_1] ADD [x3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Table_1_x3]  DEFAULT test

    GO

     

    I prefer to add GO after each T-SQL instruction

     

    I hope that post will help you

     

    Have a nice day

    Friday, January 18, 2008 2:13 PM