SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ArrangementLineArrangementLineXTab]') AND type in (N'U'))
BEGIN
  CREATE TABLE [dbo].[ArrangementLineArrangementLineXTab]
  (
    [FK_ArrangementLineNumber] [int] NOT NULL,
    [FK_ArrangementLineNumberSub] [int] NOT NULL,
    [RelationType] [int] NOT NULL,
    CONSTRAINT [PK_ArrangementLineArrangementLineXTabNew] PRIMARY KEY CLUSTERED 
    (
      [FK_ArrangementLineNumber] ASC,
      [FK_ArrangementLineNumberSub] ASC,
      [RelationType] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[ArrangementLineArrangementLineXTab]  WITH CHECK ADD  CONSTRAINT [FK_ArrangementLineArrangementLineXTab_ArrangementLines] FOREIGN KEY([FK_ArrangementLineNumber])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineNumber])

  ALTER TABLE [dbo].[ArrangementLineArrangementLineXTab] CHECK CONSTRAINT [FK_ArrangementLineArrangementLineXTab_ArrangementLines]

  ALTER TABLE [dbo].[ArrangementLineArrangementLineXTab]  WITH CHECK ADD  CONSTRAINT [FK_ArrangementLineArrangementLineXTab_ArrangementLines1] FOREIGN KEY([FK_ArrangementLineNumberSub])
  REFERENCES [dbo].[ArrangementLines] ([ArrangementLineNumber])

  ALTER TABLE [dbo].[ArrangementLineArrangementLineXTab] CHECK CONSTRAINT [FK_ArrangementLineArrangementLineXTab_ArrangementLines1]
END
ELSE
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'RelationType'
    WHERE SysObjects.Name = 'ArrangementLineArrangementLineXTab'
  )
  BEGIN
    ALTER TABLE ArrangementLineArrangementLineXTab ADD RelationType INT NULL
  END
END
GO

UPDATE ArrangementLineArrangementLineXTab
SET RelationType = 0
WHERE RelationType IS NULL
GO

ALTER TABLE ArrangementLineArrangementLineXTab ALTER COLUMN RelationType INT NOT NULL
GO

IF EXISTS
(
  SELECT * FROM sysobjects 
  WHERE name = 'PK_ArrangementLineArrangementLineXTab' 
  AND parent_obj = OBJECT_ID('ArrangementLineArrangementLineXTab')
)
BEGIN
  ALTER TABLE [dbo].[ArrangementLineArrangementLineXTab] DROP CONSTRAINT [PK_ArrangementLineArrangementLineXTab]

  ALTER TABLE [dbo].[ArrangementLineArrangementLineXTab] ADD  CONSTRAINT [PK_ArrangementLineArrangementLineXTabNew] PRIMARY KEY CLUSTERED 
  (
    [FK_ArrangementLineNumber] ASC,
    [FK_ArrangementLineNumberSub] ASC,
    [RelationType] ASC
  )
END