Original Question: Database Design: need composite key + foreign key
Here's what I need:
- An auto incrementing value for
Id
- Columns
a
andb
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.
精彩评论