Database Design: need composite key + foreign key Part 2

Original Question: Database Design: need composite key + foreign key

Here's what I need:

• An auto incrementing value for Id
• Columns a and b to be treated as a composite key

SQL:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`foo`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`foo` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `a` VARCHAR(45) NOT NULL ,
  `b` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `a_UNIQUE` (`a` ASC) ,
  UNIQUE INDEX `b_UNIQUE` (`b` ASC) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

开发者_如何学运维This insert should work because the values for a,b are different:

INSERT INTO `mydb`.`foo`
  (`id`, `a`, `b`)
VALUES 
  (NULL, 'hello world', '1' ),
  (NULL, 'hello world', '2' ) 

Why doesn't the SQL work?

You need to use:

CREATE  TABLE IF NOT EXISTS `mydb`.`foo` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `a` VARCHAR(45) NOT NULL ,
  `b` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `a_UNIQUE` (`a` ASC, b ASC)
ENGINE = InnoDB;

Your CREATE TABLE statement is creating independent unique constraints -- you want a composite constraint, like you see in my answer, to be able to have pairs being unique.

And you only need backticks (`) around MySQL reserved words to escape them.