Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    18

    Unanswered: How do I simulate 'auto_increment' on a non-primary key field

    I have this table

    Code:
    CREATE TABLE wall_comments (commentID int unsigned NOT NULL auto_increment,
     wallmemberID int unsigned NOT NULL,
     topicID  int unsigned NOT NULL,
     posterID int unsigned  NOT NULL,
     posted DATETIME NOT NULL,
     comment varchar(350),
     primary key(commentID),
     unique key(wallmemberID, topicID, posterID, posted),
    constraint foreign key (wallmemberID) references member_info(memberID),
    constraint foreign key (posterID) references member_info(memberID)
    ) ENGINE = InnoDB;
    I want wall_comments(topicID) to behave like an auto_increment field does. That is, I'd like to be able to do
    Code:
    INSERT INTO wall_comments(commentID, wallmemberID, topicID, posterID, posted, comment) VALUES('', 1, '', 10, now(), 'Some comment text');
    Is there a way to does this that is safe when simultaneous INSERT occur, and there is a possible race conditon, short of using a transaction and doing:
    Code:
    select MAX(topciID)+1 as new_topicID
    thanks,
    Kurt

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    frankly, i don't get it

    if you want both commentID and topicID to auto-increment, then one of them is redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    18
    auto_increment maybe wasn't the correct term to use. There can be several commentIDs per topicID.
    But when a new topic is started, I need a new topicID, which I currently do this way
    Code:
      $query = "SELECT MAX(topicID) + 1 as newTopicID from wall_comments where   wall_comments.wallmemberID=$memberID;";
    
      $result = mysql_query($query); 
    
      $row = mysql_fetch_assoc($result); 
    
      newTopicID = $row['newTopicID'];
    
      // $data['comment'' is submitted form data
      $comment = mysql_real_escape_string($data['comment']);
    
      //. . .set appropriate variables for INSERT
    
    
      // commentID, wallmemberID, topicID, posterID, posted, comment
      $query = "INSERT INTO wall_comments VALUES('', $wallmemberID, $newTopicID, $posterID, now(), '$comment');";
    
      $result = mysql_query($query);
    
      if ($result === FALSE) {
    
            $error =  mysql_error()
            //. ..
      }
    But doesn't this code, then, need to use a transaction to make it atomic? I just wondered if there was a way to do something like

    INSERT INTO wall_comments VALUES('', $wallmemberID, '', $posterID, now(), '$comment')

    that would cause MySQL to automatically generate a new unique topicID, when no topicID is supplied?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kkrueck View Post
    But when a new topic is started, I need a new topicID...
    you don't have a topics table?

    and how do you know when a new topic is started?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    18
    No, I don't have a topic table. The predicate for that 'wall_comments' table is

    'topicID' is the thread of discussion, on the wall of member 'wallmemberID', onto which member 'posterID' posted comment 'comment' at time 'posted'

    commentID is an extra key that I added when I mapped the relvar to MySQL

    IF you go to kkrueckeberg.userworld.com/social and sign in (it is just a demo site) and login as 'kurtk@foobox.com' with password 'abc123'. Then click on Wall. You will see a 'Start New Topic Form'. That is how a user starts a new topic.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    As Rudy mentioned you could have a topics table and let the topicid be an autoincrementing number, then have the one in your qll comments table.
    Dave

  7. #7
    Join Date
    Jul 2009
    Posts
    18
    Is it really worth having a table that has just a one attribute?

  8. #8
    Join Date
    Jul 2009
    Posts
    18
    I guess it would make sense to have a 'topics' table if the topics table also had a 'last_updated' attribute of DATETIME, indicating when the thread was most recently updated. Then I wouldn't need the subquery, involving 'MAX(posted) ... GROUP BY(topicID)' to get the most recently updated thread:
    Code:
    SELECT wc.posted
         , wc.topicID
         , wc.posterID
         , mi.first_name
         , mi.last_name 
      FROM wall_comments AS wc
    INNER
      JOIN ( SELECT topicID
                  , MAX(posted) AS latest
               FROM wall_comments
             GROUP
                 BY topicID ) AS mmm
        ON mmm.topicID = wc.topicID
    INNER
      JOIN member_info AS mi 
        ON mi.memberID = wc.posterID 
    ORDER 
        BY mmm.latest DESC
         , posted DESC
    Last edited by kkrueck; 02-28-10 at 22:44.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mmm, that query looks familiar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2009
    Posts
    18
    Yes, I bet that query does look familiar. And I now realize that wallmemberID is functionally dependent on topicID, because each topicID will be on one and only one member's wall (of the social network). wall_coments therefore isn't even in Boyce Codd normal form because the topicID is not a super key of wall_comments. I decomposed wall_comments into wall_topics and wall_comments.
    Code:
    create table wall_topics(topicID int unsigned NOT NULL auto_increment,
    wallmemberID int unsigned NOT NULL,
    primary key (topicID),
    foreign key (wallmemberID) reference member_info(memberID));
    
    create table wall_comments(commentID int unsigned NOT NULL, auto_increment,
    topicID int unsigned NOT NULL,
    posterID int unsigned NOT NULL,
    posted DATETIME NOT NULL,
    comment varchar(350),
    primary key(commentID),
    unique key(topicID, posterID, posted),
    foreign key(topicID) references wall_topics(topicID),
    foreign key(posterID) references member_info(memberID));
    Now I need to re-visit the query to see if i too needs changing. Thanks for the replies!
    Last edited by kkrueck; 03-01-10 at 12:36.

Tags for this Thread

Posting Permissions

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