From: Date: June 14 2006 9:35am
Subject: Re: Reset (or Defrag) the AUTO_INCREMENT columns
List-Archive: http://lists.mysql.com/mysql/198896
Message-Id: <448FBC45.3060302@web.de>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms090202000401080505030103"

--------------ms090202000401080505030103
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi wolverine,

of course you could "defrag" your autoincrement-values, but there's
no automation for that - you've do do that via normal insert/update
statements.
Perhaps you'll need an intermediate table.

But:
In most cases the autoincrement-value is used as an id (as in your case)
- in
db-language it is often the (primary) key - which normaly is never ever
changed
through the live-time of a data-record. If you change your primary key
you'll
have to change all references to that key in your detail-tables.

Greetings,
  Marco

wolverine my schrieb:
> Hi!
>
> I have the following tables and the data,
>
> CREATE TABLE category (
>    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
>    name VARCHAR(50) NOT NULL
> );
>
> CREATE TABLE user (
>    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>    name VARCHAR(50) NOT NULL,
>    category TINYINT UNSIGNED REFERENCES category(id)
> );
>
>
> SELECT * FROM category;
> +----+------------+
> | id | name       |
> +----+------------+
> |  1 | Classic    |
> |  2 | Gold       |
> |  5 | Platinum   |
> |  6 | Blacklist  |
> +----+------------+
>
> SELECT * FROM user;
> +----+------------+----------+
> | id | name       | category |
> +----+------------+----------+
> |  2 | John       | 1        |
> |  3 | Mark       | 2        |
> |  5 | Kenneth    | 5        |
> |  6 | Sammy      | 6        |
> |  8 | Jane       | 5        |
> +----+------------+----------+
>
>
> Based on the above, the values of both ids are defragmented.
> The category.id 3 and 4 are deleted and
> the user.id 1, 4 and 7 are deleted.
>
> May I know if there is any way we can reset (or defrag?) the values so
> that they look like the following?
>
>
> SELECT * FROM category;
> +----+------------+
> | id | name       |
> +----+------------+
> |  1 | Classic    |
> |  2 | Gold       |
> |  3 | Platinum   |
> |  4 | Blacklist  |
> +----+------------+
>
> SELECT * FROM user;
> +----+------------+----------+
> | id | name       | category |
> +----+------------+----------+
> |  1 | John       | 1        |
> |  2 | Mark       | 2        |
> |  3 | Kenneth    | 3        |
> |  4 | Sammy      | 4        |
> |  5 | Jane       | 3        |
> +----+------------+----------+
>


--------------ms090202000401080505030103
Content-Type: application/x-pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIJBzCC
At4wggJHoAMCAQICEHjEVauNF13CAgkwHViYC+EwDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UE
BhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT
I1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDUxODE3MDUxN1oX
DTA3MDUxODE3MDUxN1owRDEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEhMB8G
CSqGSIb3DQEJARYSbWFyY29fc2ltb25Ad2ViLmRlMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A
MIIBCgKCAQEAzalkA+5oB+I+dz5L5fWeQmzt0ej358RzmBXJcMuZmd9nVnu6DKlanelyH/gB
rZRB38SK7VAK3xZy3fKpY8skDGry+HxM62ee0cS1pJF7WxOK52M8UPh3GRE6poY+n8+PaQ54
cw83shxv+2BTcbTkFtle2j3dSyw/ccLtBaTI3IIJ/5oQPByJnu0uJYT3Rfud6fezWOfI2vsu
d0FkHUUSo8Aj2XuQpR1htZ2YuijcmEjLgDNZetJY43Vp7ewVEodPt6CS/rbufSmfMHXeVW8r
Rc20gG3+jHetu3+IHkn2Ae9nzpV3qJtVaNkhNelHhtyfUuj6ohUs+IaHJERx3QT8uwIDAQAB
oy8wLTAdBgNVHREEFjAUgRJtYXJjb19zaW1vbkB3ZWIuZGUwDAYDVR0TAQH/BAIwADANBgkq
hkiG9w0BAQQFAAOBgQA5Eh7YBVQMQxnOzEWX8ikVU8JdAkjeOXYRqIQHOFZbdmF02S4Reylx
dW89Wo0eHtjlF8g1TDiuyr9t+vj2qUFkyvIvy3xxxaWJA4AFTeLPXpRjaqPFi3t6NFq4cdD6
/XPIYBMrlItWBinFpjTwx5wNvT4BXDa+TOQwwzOB2UQO1DCCAt4wggJHoAMCAQICEHjEVauN
F13CAgkwHViYC+EwDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRo
YXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBG
cmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MDUxODE3MDUxN1oXDTA3MDUxODE3MDUxN1owRDEf
MB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEhMB8GCSqGSIb3DQEJARYSbWFyY29f
c2ltb25Ad2ViLmRlMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAzalkA+5oB+I+
dz5L5fWeQmzt0ej358RzmBXJcMuZmd9nVnu6DKlanelyH/gBrZRB38SK7VAK3xZy3fKpY8sk
DGry+HxM62ee0cS1pJF7WxOK52M8UPh3GRE6poY+n8+PaQ54cw83shxv+2BTcbTkFtle2j3d
Syw/ccLtBaTI3IIJ/5oQPByJnu0uJYT3Rfud6fezWOfI2vsud0FkHUUSo8Aj2XuQpR1htZ2Y
uijcmEjLgDNZetJY43Vp7ewVEodPt6CS/rbufSmfMHXeVW8rRc20gG3+jHetu3+IHkn2Ae9n
zpV3qJtVaNkhNelHhtyfUuj6ohUs+IaHJERx3QT8uwIDAQABoy8wLTAdBgNVHREEFjAUgRJt
YXJjb19zaW1vbkB3ZWIuZGUwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQFAAOBgQA5Eh7Y
BVQMQxnOzEWX8ikVU8JdAkjeOXYRqIQHOFZbdmF02S4ReylxdW89Wo0eHtjlF8g1TDiuyr9t
+vj2qUFkyvIvy3xxxaWJA4AFTeLPXpRjaqPFi3t6NFq4cdD6/XPIYBMrlItWBinFpjTwx5wN
vT4BXDa+TOQwwzOB2UQO1DCCAz8wggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJ
BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEa
MBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2Vy
dmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTEr
MCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw0wMzA3MTcw
MDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUg
Q29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1h
aWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAxKY8VXNV+065ypla
HmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7dyfArhVqqP3FW
y688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/p7bRPGEE
QB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDowOKA2
oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js
MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0x
MzgwDQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYf
qi2fNi/A9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9l
X5Xa9/eH1sYITq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8xggNkMIIDYAIBATB2
MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQu
MSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQeMRVq40X
XcICCTAdWJgL4TAJBgUrDgMCGgUAoIIBwzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwG
CSqGSIb3DQEJBTEPFw0wNjA2MTQwNzM1MzNaMCMGCSqGSIb3DQEJBDEWBBQhMCvTa3iTSLfD
L9SjxbKvEUEd7TBSBgkqhkiG9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIA
gDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDCBhQYJKwYBBAGCNxAE
MXgwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkg
THRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEHjE
VauNF13CAgkwHViYC+EwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMG
A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBl
cnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEHjEVauNF13CAgkwHViYC+EwDQYJKoZIhvcN
AQEBBQAEggEAhKvS55+5yq6s8NtXFkq7iQF1WnCJdqWbR7wB9Owfs2q/Yt4qkD+VMHLWWaXr
6/JcouP6MrwaXI07krA96UHBerpcBoHirQ09jdm9tXyhktR4rOmBmochgja1HZgEW7/qQ4JV
ZSTbi/yMZWndfnZyOo4BZZUfkFfCMmhzksAN2LOhy8y5dxgxdrptt6YtPnPXYPIBe5PKih3Z
PBmCECF557WqY3iEOulauVm3xBT4+hz1Txs7Mag5MNjtdqJ7n0Sx3Pujq3eKazDh483/mrZi
bx3Lr7lpN1+pfX6UvzntQbfMqavuxSnAcgbR4Ehy1x6MhcLGQRV4U4ml6su952MPfAAAAAAA
AA==
--------------ms090202000401080505030103--