Hi,
I've a little problem with the ID field.
Every time that I delete a news from my website database the ID
associated to that news remain empty and unused because the ID pointer
continues to go on.

I don't want leave empty the ID associated with the deleted row and I
wish to use it again for the next news. How to do it?

For example:

ID NEWS
1 *****
2 *****
3 *****
4 *****

we suppose that from my website panel I want to delete the news
associated with the ID n.3.

This would be the new result:

ID NEWS
1 *****
2 *****

4 *****


Now if I add a news to the dbase it will have the ID n.5 but I wish that
the it take the place of the Id deleted (the n.3) to avoid a too many
irregular ID numeration.

What kind of query I need?
I suppose something like "ALTER TABLE newstable auto_increment=3" but in
this case is there the risk that following news overwrite the row
associated with ID n.4? I wish that the pointer skips automatically on
to next available ID and doesn't overwrite anything.

I hope that you understand what I want to explain.
Thanx and excuse for my poor english

TN

On Feb 5, 3:30 pm, Il tulipano nero <bastig...@vivlafrance.fr> wrote:
> Hi,
> I've a little problem with the ID field.
> Every time that I delete a news from my website database the ID
> associated to that news remain empty and unused because the ID pointer
> continues to go on.

This is the correct behavior. This is because if you have linked this
news item to a record in another table (ID as foreign key) without
cascading in case of deletion, you are at risk of the other record
pointing at a wrong article (the new item that uses the old ID). So
think carefully if you really want this behavior changed.

By the way, why is it so important for you? the ID should not carry
meaning. It should be a neutral reference.

Michael

>
> I don't want leave empty the ID associated with the deleted row and I
> wish to use it again for the next news. How to do it?
>
> For example:
>
> ID NEWS
> 1 *****
> 2 *****
> 3 *****
> 4 *****
>
> we suppose that from my website panel I want to delete the news
> associated with the ID n.3.
>
> This would be the new result:
>
> ID NEWS
> 1 *****
> 2 *****
>
> 4 *****
>
> Now if I add a news to the dbase it will have the ID n.5 but I wish that
> the it take the place of the Id deleted (the n.3) to avoid a too many
> irregular ID numeration.
>
> What kind of query I need?
> I suppose something like "ALTER TABLE newstable auto_increment=3" but in
> this case is there the risk that following news overwrite the row
> associated with ID n.4? I wish that the pointer skips automatically on
> to next available ID and doesn't overwrite anything.
>
> I hope that you understand what I want to explain.
> Thanx and excuse for my poor english
>
> TN

On Tue, 05 Feb 2008 15:30:49 +0100, Il tulipano nero
<bastiglia@vivlafrance.fr> wrote:

> Hi,
> I've a little problem with the ID field.
> Every time that I delete a news from my website database the ID
> associated to that news remain empty and unused because the ID pointer
> continues to go on.
>
> I don't want leave empty the ID associated with the deleted row and I
> wish to use it again for the next news. How to do it?
>
> For example:
>
> ID NEWS
> 1 *****
> 2 *****
> 3 *****
> 4 *****
>
> we suppose that from my website panel I want to delete the news
> associated with the ID n.3.
>
> This would be the new result:
>
> ID NEWS
> 1 *****
> 2 *****
>
> 4 *****
>
>
> Now if I add a news to the dbase it will have the ID n.5 but I wish that
> the it take the place of the Id deleted (the n.3) to avoid a too many
> irregular ID numeration.
>
> What kind of query I need?
> I suppose something like "ALTER TABLE newstable auto_increment=3" but in
> this case is there the risk that following news overwrite the row
> associated with ID n.4? I wish that the pointer skips automatically on
> to next available ID and doesn't overwrite anything.

One normally doesn't want this. Take this case:
1. You have news on your website.
2. The news is accessable by id, for instance: http://example.com/news/3
3. Someone links to that particular ID 3, which atm is a story about a new
plasme television.
4. You decide to delete news#3.
5. You add new news, with ID 3, and now this is about a stroll through a
meadow you once took
6. A lot of people will visit your site thinking they will read something
about a television, and instead of telling them this news no longer
exists, you present them with your meadow stroll. Not something they were
clicking on the link.

Result: disappointed visitors, content heavily changing to unrelated
topics with the same URL, not at all good advertisement for your site.

So, I'd ask you this: what is your _exact_ problem with 'irregular ID
numeration' as you put it? It should not relate to anything noteworthy for
the user except being the definitive key for that news, and that news
alone. I you wish to have a kind of enumeration of newsitems, not related
to the database, but just as a list, you can either:

1. In a HTML context, just use a ordered list with decimal type.
2. Create the numbering with the script you're accessing the DB with.
3.
SET @myvar := 0;
SELECT @myvar := @myvar + 1 as 'psuedoid',news FROM news ORDER BY id
--
Rik Wasmus

Il tulipano nero:

> Now if I add a news to the dbase it will have the ID n.5 but I wish that
> the it take the place of the Id deleted (the n.3) to avoid a too many
> irregular ID numeration.
>
> What kind of query I need?

Why don't you refrain from showing the IDs? Just show the news title,
and, if you wish, add an enumeration.

--
Erick

Hi,
thanks to all for the fast answer.
I've made the example with a "news table" but my question is not
destined really to a news table (I know the risks that you've explained
and I would never make it).

The tip is exactly for a newsletter table.

The table will contain two fields (ID+emails) and my fear is for spam
robots that could fill the database rows with fake email addresses.
So I think that to "delete" fake email rows will be a frequent and
necessary operation for me. :-/

Do you think that the necessity of "don't leave" empty ID and try to
reuse them is only a my scruple?

Thanks.

TN

On Tue, 05 Feb 2008 15:53:29 +0100, Il tulipano nero
<bastiglia@vivlafrance.fr> wrote:

> Hi,
> thanks to all for the fast answer.
> I've made the example with a "news table" but my question is not
> destined really to a news table (I know the risks that you've explained
> and I would never make it).
>
> The tip is exactly for a newsletter table.
>
> The table will contain two fields (ID+emails) and my fear is for spam
> robots that could fill the database rows with fake email addresses.
> So I think that to "delete" fake email rows will be a frequent and
> necessary operation for me. :-/
>
> Do you think that the necessity of "don't leave" empty ID and try to
> reuse them is only a my scruple?

I'd think so. How many email entries do you expect? Millions would still
not be that much of a problem.
--
Rik Wasmus

On Feb 5, 3:53 pm, Il tulipano nero <bastig...@vivlafrance.fr> wrote:
> Hi,
> thanks to all for the fast answer.
> I've made the example with a "news table" but my question is not
> destined really to a news table (I know the risks that you've explained
> and I would never make it).
>
> The tip is exactly for a newsletter table.
>
> The table will contain two fields (ID+emails) and my fear is for spam
> robots that could fill the database rows with fake email addresses.
> So I think that to "delete" fake email rows will be a frequent and
> necessary operation for me. :-/
>
> Do you think that the necessity of "don't leave" empty ID and try to
> reuse them is only a my scruple?


In any case, if you find a way to accomplish this, you have to make
the database delete any reference (foreign key) to a deleted item, so
that it never points a wrong item. (cascade)

Michael

>
> Thanks.
>
> TN

>I've a little problem with the ID field.
>Every time that I delete a news from my website database the ID
>associated to that news remain empty and unused because the ID pointer
>continues to go on.

Excellent. Don't re-use IDs. In some applications (e.g. those
with multiple users and a *private* email capability), it is highly
desirable that given one ID, it should be very difficult to guess
any other ID, leading to not using auto_increment and using IDs of,
say, 128 hexadecimal digits.

>I don't want leave empty the ID associated with the deleted row and I

Why? If you're worried about running out of IDs, use a bigint (64
bit integer). At the rate of one ID per millisecond, you might run
out before the end of your grandchildren's lifetime. But I doubt it.

>wish to use it again for the next news. How to do it?

Completely separate any number you present the user from the ID
field. If you want the items in a consistent order, put in a
postingdatetime field and sort by it. Have the code that generates
your HTML (e.g. PHP) provide its own numbering for the "ITEM" column
if you even need it at all.

>For example:
>
>ITEM NEWS
>1 &lt;a href="/news/news.cgi?id=28987598729171737833"&gt;*****&lt;/a&gt;
>2 &lt;a href="/news/news.cgi?id=85438928479328743292"&gt;*****&lt;/a&gt;
>3 &lt;a href="/news/news.cgi?id=09284743873872782732"&gt;*****&lt;/a&gt;
>
>we suppose that from my website panel I want to delete the news
>associated with the ID n.3.
>
>This would be the new result:
>
>ID NEWS
>1 *****
>2 *****
>
>4 *****
>
>
>Now if I add a news to the dbase it will have the ID n.5 but I wish that
>the it take the place of the Id deleted (the n.3) to avoid a too many
>irregular ID numeration.

I think you need to worry about a "not enough irregular ID numeration".

>What kind of query I need?
>I suppose something like "ALTER TABLE newstable auto_increment=3" but in
>this case is there the risk that following news overwrite the row
>associated with ID n.4? I wish that the pointer skips automatically on
>to next available ID and doesn't overwrite anything.

There's plenty of risk if users have URLs to pages in their browsers
and you keep re-numbering the articles out from under them.

If Google indexes a particular URL, you should never substitute a
different article under the same URL. You may expire the article and
throw up a page that says the article no longer exists, but don't
substitute an unrelated article.

>I hope that you understand what I want to explain.

Please don't want gap-free numbering.

>Thanx and excuse for my poor english