|
|
|
|
| Re: deleted primary key can't be reused? [message #181829 is a reply to message #181827] |
Sat, 08 June 2013 15:46   |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 17:47 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 19:32 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 04:46 |
gordonb.ps993
Messages: 1
Registered: June 2013
Karma: 0
|
Junior Member |
|
|
> 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 05:52 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 11:05 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
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 17:25 |
GravyCode
Messages: 2
Registered: June 2013
Karma: 0
|
Junior Member |
|
|
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 17:52 |
Marc van Lieshout
Messages: 10
Registered: March 2011
Karma: 0
|
Junior Member |
|
|
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 09:05 |
gordonb.tuxls
Messages: 1
Registered: June 2013
Karma: 0
|
Junior Member |
|
|
> 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 11:05 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]