Ha! I couldn’t find the original database where I had this particular case so I made a new example, maybe it’s slightly on the edge of practical realness but should illustrate the point well:
https://dl.dropboxusercontent.com/u/13230070/screenshots/fks-model.png
Practical usage: there are users and photos and each user can choose any number of favourite photos (user_has_photo). Each photo he adds to his favourites he can annotate with any number of labels. Each label appears on the site in shape of a rectangle and the rectangle can consist of any number of colours chosen by the user - then a rainbow-like gradient is formed from the colours the user has chosen for his label of his favourited photo.
As you can see the table user_has_photo_has_label_has_colour has a 4-column PK and a 3-column FK that connects it to user_has_photo_has_label. If user_has_photo_has_label had an additional one-column PK, then we could use it in user_has_photo_has_label_has_colour instead of the thee-column FK - and to me it would look a bit clearer.
So, what do you say?
SQL:
CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `photo` (
`id` INT NOT NULL AUTO_INCREMENT,
`filename` VARCHAR(50) NOT NULL,
`description` TEXT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `user_has_photo` (
`user_id` INT NOT NULL,
`photo_id` INT NOT NULL,
PRIMARY KEY (`user_id`, `photo_id`),
INDEX `fk_user_has_photo_photo1_idx` (`photo_id` ASC),
CONSTRAINT `fk_user_has_photo_user`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_user_has_photo_photo1`
FOREIGN KEY (`photo_id`)
REFERENCES `photo` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE `label` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `user_has_photo_has_label` (
`user_has_photo_user_id` INT NOT NULL,
`user_has_photo_photo_id` INT NOT NULL,
`label_id` INT NOT NULL,
PRIMARY KEY (`user_has_photo_user_id`, `user_has_photo_photo_id`, `label_id`),
INDEX `fk_user_has_photo_has_label_label1_idx` (`label_id` ASC),
CONSTRAINT `fk_user_has_photo_has_label_user_has_photo1`
FOREIGN KEY (`user_has_photo_user_id` , `user_has_photo_photo_id`)
REFERENCES `user_has_photo` (`user_id` , `photo_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_user_has_photo_has_label_label1`
FOREIGN KEY (`label_id`)
REFERENCES `label` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE `colour` (
`id` INT NOT NULL AUTO_INCREMENT,
`colour` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
CREATE TABLE `user_has_photo_has_label_has_colour` (
`user_has_photo_has_label_user_has_photo_user_id` INT NOT NULL,
`user_has_photo_has_label_user_has_photo_photo_id` INT NOT NULL,
`user_has_photo_has_label_label_id` INT NOT NULL,
`colour_id` INT NOT NULL,
PRIMARY KEY (`user_has_photo_has_label_user_has_photo_user_id`, `user_has_photo_has_label_user_has_photo_photo_id`, `user_has_photo_has_label_label_id`, `colour_id`),
INDEX `fk_user_has_photo_has_label_has_colour_colour1_idx` (`colour_id` ASC),
CONSTRAINT `fk_user_has_photo_has_label_has_colour_user_has_photo_has_lab1`
FOREIGN KEY (`user_has_photo_has_label_user_has_photo_user_id` , `user_has_photo_has_label_user_has_photo_photo_id` , `user_has_photo_has_label_label_id`)
REFERENCES `user_has_photo_has_label` (`user_has_photo_user_id` , `user_has_photo_photo_id` , `label_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_user_has_photo_has_label_has_colour_colour1`
FOREIGN KEY (`colour_id`)
REFERENCES `colour` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;