The value of this column should not have any significance beyond identifying the row - which means that the rest of your code should not rely on the fact that id # 25 is the 25th row. There are multiple reasons for this, the first and most important being that a relational database table is inherently unordered - you can only say that a particular row is the i-th row when using an ORDER BY clause (yes, I know that most database system will return data in a consistent order without an ORDER BY clause, but you should never rely on this fact).

It follows that the rest of your code should only treat the id value as an identifier, and use some other mechanism to count or keep track of the position of the row in the current result set.

Michael

Long answer: That is one of the most common problems while dealing with (MySQL) BDs. You could scan all places where there might be a reference to the deleted image and fix it when you delete an image, or refuse to delete the image if there are references to it. If you're using an InnoDB table, it is possible to delete recursively -- take a look at the manuals.

Short answer: It depends on what problem you have.

Not sure this is the best solution, but you could remove the id colum and then reassign it. Normaly the idea of id's is that they never change, so i'm curious as to why you want to reassign pictures to new id's just to make them a gapless sequence?

Not sure this is the best solution,

Definitely not.

Removing a primary key and recreating it will break the database referential integrity.

BTW reusing the gaps in the primary key, as the OP wants, is not the smartest move either. I know a couple of methods to achieve that, but I also know half a dozen reasons not to do it. Therefore I'll keep the method for myself.

In the meantime, I will just suggest leaving the primary key alone.

I understood about the integrity. That would be why I mentioned that normaly keys (ids in this case_ are meant to stay the same and never be changed.


___________
Eric Hodges

the reason that I use the id field is that the client wants a slide show with next,prev and exit buttons. the only way that I could figure out how to do this is passing the id for an image in an hidden field and incrementing or decrementing the id field to point to a previous or next image to be displayed. if there is a break in the id number sequence the code barfs. if I could pass id numbers that don't exist I could make the delete image option that the client wants.

How do you deal with first and last image then?

I insist on my belief that a primary key should be doing only one task, i.e. identifying the record uniquely. If you need a display id, you have two choices:

• Adding a field to your table for display purposes, but then you would have two further problems.
• filling the gaps when you delete a record.
• Always displaying the records in the same order. No chance of diplaying with different criteria.
• Creating a display number when you select the records. And you can do it either in (at least) two ways:
• with a MySQL variable.
  

  SELECT @count := @count+1 as sequence, id, name, filename from mytable
  [download]

• In your Perl script.
  

  my $query = "SELECT id, name, filename from mytable";
  my $sth=$dbh->prepare($query);
  $sth->execute();
  my @results;
  my $count =0;
  while (my $row = $sth->fetchrow_arrayref) {
      push @results, [ $count++ , @$row ]
  }
  [download]

  Now your @results have a counter that you can use for displaying purposes.
  Modifying your query with a different WHERE or ORDER BY will change the display order.

The correct way to do this is with appropriate WHERE clauses.

For example to get the next row in the sequence:

select ... from the_table 
 where id = 
     (select min(id) from the_table where id > $last_id)
[download]

An alternative, with MySQL, is to use the LIMIT keyword. IIRC this should work (note - I'm not a MySQL specialist, so check for correct syntax):

select ... from the_table where id > $last_id order by id LIMIT 1
[download]

As you are limiting the result set to a single row, and will get next row in the sequence even if there are gaps.

Michael

jwlarson3rd you asked a very similar, if not identical, question here. And I'll respond in kind by repeating the reply I gave then. Like the other monks have pointed out, an unbroken sequence of ids is unnecessary. And though there are potential holes in my method of displaying records with Next and Prev buttons (I'm relying on the DB returning records in order), I have not experienced problems. However, to shore it up a bit (to address the concerns of mpeppler) you may be able to do some housekeeping with a hidden input statement, cookie, or flat file).

To your point of reclaiming lost ids, I have often wondered what happens if I'm using a datatype of TINYINT and exceed 256 because of deleting and adding records. So I have errored on the side of using datatypes that far exceed my needs.

—Brad
"A little yeast leavens the whole dough."