DECLARE @SQL VARCHAR(4000)
SET @SQL = 'ALTER TABLE ReservationNotes DROP CONSTRAINT |ConstraintName| '
SET @SQL = REPLACE(@SQL, '|ConstraintName|', (SELECT Name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID('ReservationNotes')))
EXEC (@SQL)

IF EXISTS 
(
  SELECT * FROM sysobjects 
  WHERE name = 'FK_reservationNotes_Customer' 
  AND parent_obj = OBJECT_ID('ReservationNotes')
)
BEGIN
  SET @SQL = 'ALTER TABLE ReservationNotes DROP CONSTRAINT FK_ReservationNotes_Customer '
  EXEC (@SQL)
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_HotelCustomerNumber'
    WHERE SysObjects.Name = 'ReservationNotes'
)
  ALTER TABLE ReservationNotes ADD FK_HotelCustomerNumber INT NULL 
GO

UPDATE ReservationNotes 
SET FK_HotelCustomerNumber = (
  SELECT TOP 1 CustomerNumber FROM ReservationMain rm (nolock)
  INNER JOIN Customer c (NOLOCK) ON rm.HotelNumber = c.HotelNumber
    AND CustomerTypeNumber = 7
    --AND ISNULL(c.MarkAsDeleted, 0) = 0
  WHERE rm.ReferenceNumber = ReservationNotes.ReferenceNumber
)

UPDATE ReservationNotes SET FK_HotelCustomerNumber = (
  SELECT TOP 1 CustomerNumber
  FROM Customer (NOLOCK)
  WHERE CustomerTypeNumber = 7
  AND LocalHotel = 1
  AND ISNULL(Customer.MarkAsDeleted, 0) = 0
)
WHERE ReservationNotes.FK_HotelCustomerNumber IS NULL

IF @@ROWCOUNT > 0
BEGIN
  EXEC sp_executesql N'ALTER TABLE ReservationNotes ALTER COLUMN FK_HotelCustomerNumber INT NOT NULL'
 
  EXEC sp_executesql N'ALTER TABLE ReservationNotes ADD CONSTRAINT PK_RESERVATIONNOTES PRIMARY KEY
  (
    [ReferenceNumber] DESC,
    [GuestNumber] ASC,
    [NotePage] ASC,
    [FK_HotelCustomerNumber] ASC
  )'

END