Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Package

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)
and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

When I define PackageVersion (read PackageVariant) table, I mark VersionNumber (aka idPackageVariant) as Auto Increment and a part of Primary Key in the order defined above:

PRIMARY KEY (idHolidayPackage, idPackageVariant)

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 ,

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

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

  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;

However, due to this bug in mySql, I need to have AutoIncrement Key as the first part of the Primary Key in a compound key.

So, does the order of keys in compound Primary key make any difference ?

Just for reference, the above design comes from here.

share|improve this question
    
You can't have an auto-incremented column as part of a compound Primary Key in InnoDB. Unfortunately. The VersionNumbers have to be supplied by the application (or a trigger). –  ypercube Mar 23 '12 at 15:16
    
You can have VersionNumber as auto_increment and PK and (PackageID, VersionNumber) as Unique. Or vice-versa. But that's not what VersionNumber is supposed to be, isn't it? –  ypercube Mar 23 '12 at 15:19
    
@ypercube Shouldn't the combination of PackageId and VersionNumber be unique to identify a unique variant for a given package ? If I make VersionNumber auto_increment, the combination becomes unique. Also, the unique combination helps in identifying a unique variant for a given package also. –  brainydexter Mar 23 '12 at 18:32
    
It's a One:Many relationship between Package:PackageVariant –  brainydexter Mar 23 '12 at 18:33
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
I will mostly do a lookup on PackageVariant based on idHolidayPackage, so it makes sense for keeping idHolidayPackage as the first part in the the compound key. –  brainydexter Mar 23 '12 at 18:58
    
I don't understand what a clustered index is, so I didn't understand what you wrote about clustered index. However, as for the snippet posted by you, it won't work in MySQL due to the bug I mentioned in my question. –  brainydexter Mar 23 '12 at 19:00
    
Also, if I just have idPackageVariant as the primary key, then as you suggested, the db would then do a scan for idHolidayPackage everytime I access a variant using idHolidayPackage ? –  brainydexter Mar 23 '12 at 19:03
    
what do you think ? –  brainydexter Mar 25 '12 at 7:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.