Does the order of keys in compound Primary key make any difference?
The order of keys can make a difference depending on your access pattern.

Example...

You want to find MAX(idPackageVariant) for a singleidHolidayPackage

With idHolidayPackage as the second column, then the index will be scanned. With idHolidayPackage as the first, it can be seeked which is more efficient.

This is a specifiy MySQL problem of course. IIRCl you'll need to do this. InnoDB uses the first unique key as the clustered index which may or may not be the primary key. However, this may not be the case

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,

  -- not needed INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


Alternatively, you can just have idPackageVariant as the primary and clustered key for efficiency.