SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Switzerland
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question is it possible to have "auto_increment" twice in a mysql-table?

    I have two tables in a mysql-table and I want to save some things about events. So I use the first table for the headings like the date, time, location, ... (things that are the same for the whole event). In the second table I want to store details about each particulary event.

    In the first table, there is a field named "id" (auto_increment AND unique). I related the second table to the first one using this field. Besites there is a "id"-field for this table as well (auto_increment AND unique).

    So the problem is, that I want to have another field in the second tabe (auto_increment AND NOT unique) which stores a number 1, 2, 3, 4, 5, ..) that starts from 1 again for every new entry in the first table ...

    Is there a function or somthing similar in mysql or php?

    Thanks in advance for every tipp!

    tg

  2. #2
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Auto-increment can only be used on the primary key if I remember correctly.

  3. #3
    SitePoint Addict moonchild's Avatar
    Join Date
    Nov 2003
    Location
    U$A
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Phil.Roberts
    Auto-increment can only be used on the primary key if I remember correctly.
    correct. you can only use auto_increment one time in a table to my knowledge.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    auto_increment only once, but it doesn't have to be the primary key
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    hey guess what, events is a reserved word

    here's some testing which shows how to get the auto_increment to "start over" as the second column in a compound primary key, when the first column value changes

    note the eventdetails table would not need its own auto_increment key (which i believe is what prompted the question)
    Code:
    create table events
    ( id integer not null primary key auto_increment
    , name varchar(10)
    );
    insert into events (name) values
     ( 'foo' )
    ,( 'bar' )
    ,( 'qux' )
    ;
    create table eventdetails
    ( event_id integer not null
    , id integer not null auto_increment
    , primary key (event_id,id)
    , name varchar(10)
    );
    insert into eventdetails (event_id, name) values
     ( 1,'fooa' )
    ,( 1,'foob' )
    ,( 1,'fooc' )
    ,( 1,'food' )
    ,( 2,'barbq' )
    ,( 3,'quxlux' )
    ,( 3,'quxsux' )
    ,( 3,'quxtux' )
    ;
    select events.id 
         , events.name
         , eventdetails.id
         , eventdetails.name
      from `events`
    inner 
      join eventdetails
        on events.id = eventdetails.event_id
    order 
        by events.id    
         , eventdetails.id
        
    id  name  id  name  
    1 foo 1 fooa 
    1 foo 2 foob 
    1 foo 3 fooc 
    1 foo 4 food 
    2 bar 1 barbq 
    3 qux 1 quxlux 
    3 qux 2 quxsux 
    3 qux 3 quxtux
    note the backticks around the reserved word were necessary in the SELECT, but only in the FROM clause, and not necessary in the CREATE or INSERT statements

    oh, the goodness and wisdom of mysql syntax...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •