FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » deleted primary key can't be reused?
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
deleted primary key can't be reused? [message #181827] Sat, 08 June 2013 10:24 Go to next message
richard is currently offline  richard   
Messages: 213
Registered: June 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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.
Re: deleted primary key can't be reused? [message #181828 is a reply to message #181827] Sat, 08 June 2013 10:57 Go to previous messageGo to next message
richard is currently offline  richard   
Messages: 213
Registered: June 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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.
Re: deleted primary key can't be reused? [message #181829 is a reply to message #181827] Sat, 08 June 2013 11:46 Go to previous messageGo to next message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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...
Re: deleted primary key can't be reused? [message #181830 is a reply to message #181827] Sat, 08 June 2013 13:47 Go to previous messageGo to next message
The Natural Philosoph is currently offline  The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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.
Re: deleted primary key can't be reused? [message #181831 is a reply to message #181827] Sat, 08 June 2013 15:32 Go to previous messageGo to next message
Norman Peelman is currently offline  Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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?-
Re: deleted primary key can't be reused? [message #181843 is a reply to message #181827] Wed, 12 June 2013 00:46 Go to previous messageGo to next message
gordonb.ps993 is currently offline  gordonb.ps993
Messages: 1
Registered: June 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
> 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.
Re: deleted primary key can't be reused? [message #181844 is a reply to message #181843] Wed, 12 June 2013 01:52 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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(at)gmail(dot)com
Re: deleted primary key can't be reused? [message #181845 is a reply to message #181827] Wed, 12 June 2013 07:05 Go to previous messageGo to next message
Doug Miller is currently offline  Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
richard <noreply(at)example(dot)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....
Re: deleted primary key can't be reused? [message #181902 is a reply to message #181827] Fri, 21 June 2013 13:25 Go to previous messageGo to next message
GravyCode is currently offline  GravyCode
Messages: 2
Registered: June 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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;
Re: deleted primary key can't be reused? [message #181903 is a reply to message #181827] Fri, 21 June 2013 13:52 Go to previous messageGo to next message
Marc van Lieshout is currently offline  Marc van Lieshout
Messages: 10
Registered: March 2011
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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.
Re: deleted primary key can't be reused? [message #181916 is a reply to message #181902] Sun, 23 June 2013 05:05 Go to previous messageGo to next message
gordonb.tuxls is currently offline  gordonb.tuxls
Messages: 1
Registered: June 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
> 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.
Re: deleted primary key can't be reused? [message #181917 is a reply to message #181916] Sun, 23 June 2013 07:05 Go to previous message
The Natural Philosoph is currently offline  The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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.
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Read info from webcam video
Next Topic: PHP form generator
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Oct 20 19:28:18 EDT 2017

Total time taken to generate the page: 0.00926 seconds