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

kkrueck · #1 (**permalink**) 02-26-10, 13:54

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

r937 · #2 (**permalink**) 02-26-10, 23:19

frankly, i don't get it

if you want both commentID and topicID to auto-increment, then one of them is redundant

kkrueck · #3 (**permalink**) 02-27-10, 20:58

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?

r937 · #4 (**permalink**) 02-27-10, 21:21

Quote:

Originally Posted by kkrueck

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?

kkrueck · #5 (**permalink**) 02-27-10, 22:15

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.

dav1mo · #6 (**permalink**) 02-28-10, 21:54

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

kkrueck · #7 (**permalink**) 02-28-10, 22:32

Is it really worth having a table that has just a one attribute?

kkrueck · #8 (**permalink**) 02-28-10, 22:40

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

r937 · #9 (**permalink**) 02-28-10, 23:30

mmm, that query looks familiar

kkrueck · #10 (**permalink**) 03-01-10, 11:48

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech