IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.Id 
    AND SysColumns.Name = 'ReferenceNumber'
    AND SysObjects.Name = 'ForecastDaily'
)
BEGIN
  IF EXISTS (SELECT name FROM SysIndexes(NOLOCK) WHERE name = 'PK_ForecastDaily')
  BEGIN
	ALTER TABLE ForecastDaily DROP CONSTRAINT PK_ForecastDaily
  END
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.Id 
    AND SysColumns.Name = 'ReferenceNumber'
    AND SysObjects.Name = 'ForecastDaily'
)
  ALTER TABLE ForecastDaily ADD ReferenceNumber INT NULL
GO

UPDATE ForecastDaily SET ReferenceNumber = -1 WHERE ReferenceNumber IS NULL
GO

ALTER TABLE ForecastDaily ALTER COLUMN ReferenceNumber INT NOT NULL
GO

ALTER TABLE ForecastDaily WITH NOCHECK 
ADD CONSTRAINT PK_ForecastDaily PRIMARY KEY CLUSTERED 
(
  [AccountNumber] ASC,
  [Date] ASC,
  [HotelCustomerNumber] ASC,
  [FK_DepartmentNumber] ASC,
  [ReferenceNumber] ASC
)
GO