Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #9778 > unrolled thread

deleted primary key can't be reused?

Back to article view | Back to comp.lang.php

Contents

  deleted primary key can't be reused? richard <noreply@example.com> - 2013-06-08 10:24 -0400
    Re: deleted primary key can't be reused? richard <noreply@example.com> - 2013-06-08 10:57 -0400
    Re: deleted primary key can't be reused? Luuk <luuk@invalid.lan> - 2013-06-08 17:46 +0200
    Re: deleted primary key can't be reused? The Natural Philosopher <tnp@invalid.invalid> - 2013-06-08 18:47 +0100
    Re: deleted primary key can't be reused? Norman Peelman <npeelman@cfl.rr.com> - 2013-06-08 15:32 -0400
    Re: deleted primary key can't be reused? gordonb.ps993@burditt.org (Gordon Burditt) - 2013-06-11 23:46 -0500
      Re: deleted primary key can't be reused? Denis McMahon <denismfmcmahon@gmail.com> - 2013-06-12 05:52 +0000
    Re: deleted primary key can't be reused? Doug Miller <doug_at_milmac_dot_com@example.com> - 2013-06-12 11:05 +0000
    Re: deleted primary key can't be reused? GravyCode <matty023@gmail.com> - 2013-06-21 10:25 -0700
      Re: deleted primary key can't be reused? gordonb.tuxls@burditt.org (Gordon Burditt) - 2013-06-23 04:05 -0500
        Re: deleted primary key can't be reused? The Natural Philosopher <tnp@invalid.invalid> - 2013-06-23 12:05 +0100
    Re: deleted primary key can't be reused? Marc van Lieshout <invalid@example.com> - 2013-06-21 19:52 +0200

#9778 — deleted primary key can't be reused?

Entered 100 items into 2 columns each.
Deleted 2.
Upon entering new record using id of 98, no can do.
The next new record number becomes 101.
"Duplicate key error" is issued.
How do I take back the deleted key number so I can use it again?
No, I do not mean the data that is in the record.
To me, once a record is deleted, the key index should reflect that the
record no longer exists, so the key index should be -1 of what it was.
Not continue its esistance.

[toc] | [next] | [standalone]

#9779

On Sat, 8 Jun 2013 10:24:27 -0400, richard wrote:

> Entered 100 items into 2 columns each.
> Deleted 2.
> Upon entering new record using id of 98, no can do.
> The next new record number becomes 101.
> "Duplicate key error" is issued.
> How do I take back the deleted key number so I can use it again?
> No, I do not mean the data that is in the record.
> To me, once a record is deleted, the key index should reflect that the
> record no longer exists, so the key index should be -1 of what it was.
> Not continue its esistance.

well at least I got the keys back and reusable for now.

[toc] | [prev] | [next] | [standalone]

#9780

On 08-06-2013 16:24, richard wrote:
> Entered 100 items into 2 columns each.
> Deleted 2.
> Upon entering new record using id of 98, no can do.
> The next new record number becomes 101.
> "Duplicate key error" is issued.
> How do I take back the deleted key number so I can use it again?
> No, I do not mean the data that is in the record.
> To me, once a record is deleted, the key index should reflect that the
> record no longer exists, so the key index should be -1 of what it was.
> Not continue its esistance.
>

while this seems to be a mysql question,
It would be better to post the output of
show create table <tablename>

An index that is set to 'auto_increment' is always auto-incremented...

[toc] | [prev] | [next] | [standalone]

#9781

On 08/06/13 15:24, richard wrote:
> Entered 100 items into 2 columns each.
> Deleted 2.
> Upon entering new record using id of 98, no can do.
> The next new record number becomes 101.
> "Duplicate key error" is issued.
> How do I take back the deleted key number so I can use it again?
> No, I do not mean the data that is in the record.
> To me, once a record is deleted, the key index should reflect that the
> record no longer exists, so the key index should be -1 of what it was.
> Not continue its esistance.
there is a way to do this because I have. You can renumber all the IDS 
but it aint trivial.

But the point about a proper unique primary key is that it is forever 
associated with a particular record. Beware of what you wish for.

-- 
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to lead are elected by the least capable of producing, and where the members of society least likely to sustain themselves or succeed, are rewarded with goods and services paid for by the confiscated wealth of a diminishing number of producers.

[toc] | [prev] | [next] | [standalone]

#9782

On 06/08/2013 10:24 AM, richard wrote:
> Entered 100 items into 2 columns each.
> Deleted 2.
> Upon entering new record using id of 98, no can do.
> The next new record number becomes 101.
> "Duplicate key error" is issued.
> How do I take back the deleted key number so I can use it again?
> No, I do not mean the data that is in the record.
> To me, once a record is deleted, the key index should reflect that the
> record no longer exists, so the key index should be -1 of what it was.
> Not continue its esistance.
>

   That's not necessarily how it works. If you think about what you are 
saying you should come to the realisation that the database would have 
to reorder all the higher keys every time you deleted something. Not 
good. You -can- reuse 'empty' indexes by either manually inserting the 
new data or programming to look for them when inserting data, again not 
good. You are better off to let the database do its job and let it 
manage the keys/indexes for you.

-- 
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-

[toc] | [prev] | [next] | [standalone]

#9794

> Entered 100 items into 2 columns each.
> Deleted 2.

Which two?  What were their primary keys?

> Upon entering new record using id of 98, no can do.
> The next new record number becomes 101.
> "Duplicate key error" is issued.

Assuming the records you deleted had primary keys 42 and 86, this
is correct.

If you deleted record 98, you should be able to re-insert it.

> How do I take back the deleted key number so I can use it again?

You want *all* the records renumbered?  I'd really hate it if banks
did that with account numbers, so which account is mine keeps
changing, and I keep having to have checks reprinted.  Other tables
are likely to refer to records in this table by its primary key.
If you keep changing that, you get a mess.  When is that useful?
I can think of plenty of business circumstances where it would be
a crime.

> No, I do not mean the data that is in the record.

> To me, once a record is deleted, the key index should reflect that the
> record no longer exists, so the key index should be -1 of what it was.
> Not continue its esistance.

In other words, if the primary key for a record is "Mary Contrary",
and you delete that, and the next record is "Michael Jackson", the
primary key for that record should now become "Mary Contrary"?

Primary keys are not, in general, supposed to change.  That may not
correspond well to real-world reality.  Account numbers may have
to change when banks merge and there are now duplicate account
numbers for the merged bank.  Names (which make poor primary keys)
may change on marriage, adoption, etc.  If you want something in
an order, add a column for the attribute you are sorting on (say,
song sales) and use ORDER BY.

[toc] | [prev] | [next] | [standalone]

#9795

On Tue, 11 Jun 2013 23:46:48 -0500, Gordon Burditt wrote:

> In other words, if the primary key for a record is "Mary Contrary",
> and you delete that, and the next record is "Michael Jackson", the
> primary key for that record should now become "Mary Contrary"?

I don't think that's what Richard is asking for.

What Richard wants to do is delete the record with primary key value of 
x, and at a later date, insert a new record with the primary key value of 
x.

The underlying problem here is that Richard created the table using some 
combination of options that he read about on a web page 10 years ago, or 
possibly chose using a random number generator of some sort, and whatever 
options he had set the table concerned up with would not allow him to 
reuse a deleted primary key value.

Richard being Richard he's miraculously solved the problem, which 
probably means he's removed every attribute he can from the affected 
column until he could make the change he wanted to make, and he may have 
then put a random selection of attributes back on the table, some of 
which might bear some resemblance to those he removed in the first place.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#9796

richard <noreply@example.com> wrote in news:1riuk3ca2113p$.1butqaatdq7hw.dlg@
40tude.net:

> Entered 100 items into 2 columns each.
> Deleted 2.
> Upon entering new record using id of 98, no can do.

Did you perhaps delete records 99 and 100? 

> The next new record number becomes 101.
> "Duplicate key error" is issued.

Apparently because you didn't delete record 98....

[toc] | [prev] | [next] | [standalone]

#9853

On Saturday, June 8, 2013 10:24:27 PM UTC+8, richard wrote:
> Entered 100 items into 2 columns each.
> 
> Deleted 2.
> 
> Upon entering new record using id of 98, no can do.
> 
> The next new record number becomes 101.
> 
> "Duplicate key error" is issued.
> 
> How do I take back the deleted key number so I can use it again?
> 
> No, I do not mean the data that is in the record.
> 
> To me, once a record is deleted, the key index should reflect that the
> 
> record no longer exists, so the key index should be -1 of what it was.
> 
> Not continue its esistance.

Just reset the auto increment to 1 and it should jump to the next available empty number, if your first untaken id is 98, then the next insert will take that position.

ALTER TABLE `table_name` AUTO_INCREMENT =1;

[toc] | [prev] | [next] | [standalone]

#9867

> Just reset the auto increment to 1 and it should jump to the next available empty number, if your first untaken id is 98, then the next insert will take that position.
> 
> ALTER TABLE `table_name` AUTO_INCREMENT =1;

Funny, that statement, followed by SHOW CREATE `table_name`,
seems to do nothing successfully.  On both MyISAM and InnoDB tables,
MySQL 5.5.31, the auto_increment value doesn't change.  I can change
the value to a value max(`column_name`)+1 or higher, but not back to 1.

I still think it used to work at some point.  What I'm not sure about
is the part about "jump to the next available empty number", but now
it won't let me set up a test for that.

[toc] | [prev] | [next] | [standalone]

#9868

On 23/06/13 10:05, Gordon Burditt wrote:
>> Just reset the auto increment to 1 and it should jump to the next available empty number, if your first untaken id is 98, then the next insert will take that position.
>>
>> ALTER TABLE `table_name` AUTO_INCREMENT =1;
> Funny, that statement, followed by SHOW CREATE `table_name`,
> seems to do nothing successfully.  On both MyISAM and InnoDB tables,
> MySQL 5.5.31, the auto_increment value doesn't change.  I can change
> the value to a value max(`column_name`)+1 or higher, but not back to 1.
>
> I still think it used to work at some point.  What I'm not sure about
> is the part about "jump to the next available empty number", but now
> it won't let me set up a test for that.
this works

mysql_query("delete from orders");
mysql_query("alter table orders auto_increment=1");

I used it to  reset a table to empty., but you cant reset a table with a 
mandatory index applied that isn't empty.,

You could create a temp table, copy the data to it, empty and reset the 
original and then copy the data back...

I ran into this when trying to merge data from portable devices  to the 
main database. Read the moving data on te portable device and update the 
main database with it, then clear all the tables in the portable 
database and copy the full tables from the main one onto it, that it 
needed to use.

I had to do that otherwise the data copy couldn't take place due to 
overlapping IDs

I used to make a file in CSV formatfrom the main database including IDs 
an LOAD FILE into the empty tables. IF that found an ID already 
existing, it bombed. Even though the database had been deleted.

Perhaps someone in the SQL  group knows the chapter and verse.,



-- 
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to lead are elected by the least capable of producing, and where the members of society least likely to sustain themselves or succeed, are rewarded with goods and services paid for by the confiscated wealth of a diminishing number of producers.

[toc] | [prev] | [next] | [standalone]

#9854

On 08-06-13 16:24, richard wrote:
> Entered 100 items into 2 columns each.
> Deleted 2.
> Upon entering new record using id of 98, no can do.
> The next new record number becomes 101.
> "Duplicate key error" is issued.
> How do I take back the deleted key number so I can use it again?
> No, I do not mean the data that is in the record.
> To me, once a record is deleted, the key index should reflect that the
> record no longer exists, so the key index should be -1 of what it was.
> Not continue its esistance.
>

This is related to a problem I had once: I wanted a special record in a 
table with id 0.

insert into <table> (id, ...) values (0, ....);

didn't work; the insert was succesful, but with ID 1. Then I executed:

update <table> set id = 0 where id = 1;

That solved the problem.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web