-- vd20150702: New table dealing with customers' preferences for rooms, room types and wares

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
               WHERE 
                 id = object_id(N'[dbo].[CustomerPreferences]') AND 
                 OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

  CREATE TABLE CustomerPreferences(
    PreferenceID INTEGER NOT NULL,
    CustomerNumber INTEGER NOT NULL,
    HotelNumber INTEGER NOT NULL,
    PreferenceType TINYINT NOT NULL,
    PreferencePriority TINYINT NOT NULL)

  ALTER TABLE CustomerPreferences 
  ADD CONSTRAINT DF_CustomerPreferences_PreferencePriority 
  DEFAULT 100 FOR PreferencePriority 
	 
  ALTER TABLE CustomerPreferences
  ADD CONSTRAINT PK_CustomerPreferences PRIMARY KEY 
 (PreferenceID, CustomerNumber, HotelNumber, PreferenceType)

  ALTER TABLE CustomerPreferences ADD CONSTRAINT FK_CustomerPreferences_CustomerFKey
  FOREIGN KEY (CustomerNumber)
  REFERENCES Customer(CustomerNumber)

END


	
