List:General Discussion« Previous MessageNext Message »
From:Sebastian Mendel Date:April 22 2008 6:58am
Subject:Re: auto_increment
View as plain text  
Hiep Nguyen schrieb:
> hi list,
> 
> reading manual on mysql regarding auto_increment with multiple-column 
> index:
> 
> CREATE TABLE animals (
>     grp ENUM('fish','mammal','bird') NOT NULL,
>     id MEDIUMINT NOT NULL AUTO_INCREMENT,
>     name CHAR(30) NOT NULL,
>     PRIMARY KEY (grp,id)
> );
> 
> INSERT INTO animals (grp,name) VALUES
>     ('mammal','dog'),('mammal','cat'),
>     ('bird','penguin'),('fish','lax'),('mammal','whale'),
>     ('bird','ostrich');
> 
> SELECT * FROM animals ORDER BY grp,id;
> 
> +--------+----+---------+
> | grp    | id | name    |
> +--------+----+---------+
> | fish   |  1 | lax     |
> | mammal |  1 | dog     |
> | mammal |  2 | cat     |
> | mammal |  3 | whale   |
> | bird   |  1 | penguin |
> | bird   |  2 | ostrich |
> +--------+----+---------+
> 
> my question is what id would be if i:
> 
> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND 
> `name`='ostrich' LIMIT 1;

you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE

your key is grp,id (bird,2)

but your query will fail, because there is already grp,id (mammal,2) and 
therre can not be two identical UNIQUE (PRIMARY) keys

-- 
Sebastian
Thread
auto_incrementHiep Nguyen21 Apr
  • Re: auto_incrementSebastian Mendel22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementHiep Nguyen22 Apr
  • Re: auto_incrementBen Clewett22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementBen Clewett22 Apr
      • PerformanceBruno B .  B .  Magalhães22 Apr
        • Re: PerformancePhil22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformancePerrin Harkins22 Apr
          • Re: PerformanceBruno B .  B .  Magalhães22 Apr
            • Re: PerformanceRob Wultsch22 Apr