Zresetuj AutoIncrement w SQL Server po usunięciu


265

Usunąłem niektóre rekordy z tabeli w bazie danych SQL Server. Teraz ID zmienia się z 101 na 1200. Chcę ponownie usunąć rekordy, ale chcę, aby ID powróciły do ​​102. Czy istnieje sposób, aby to zrobić w SQL Server?


46
Proszę nie mów „nie rób tego”. Nienawidzę, kiedy pytam, jak coś zrobić, a wszystko, co dostaję, to nie. Tak, zresetowanie tożsamości może powodować problemy z kluczem obcym, ale tylko wtedy, gdy nie znasz odpowiednio bazy danych i programu. Istnieją bardzo dobre powody, aby zresetować tożsamość po zaplanowanym usunięciu - nazywają się Audytorzy. Audytorzy nie lubią dostrzegać luk, więc wypełnij je, zrób to w kontrolowany sposób i upewnij się, że utrzymywane są ograniczenia klucza obcego.

6
@spyder, czy wiesz, że będziesz mieć luki, jeśli płytka rekordu zostanie wycofana nie tylko w celu usunięcia? Nie można uniknąć luk za pomocą autowzrostu i głupotą jest spróbować. Pracowałem dla agencji audytorskiej i kompetentni audytorzy mogą im to wyjaśnić. Ponadto, jeśli masz odpowiednie tabele audytu, mogą zobaczyć, co się stało z tymi rekordami. Lub jeśli z przyczyn prawnych nie będzie żadnych luk (jest to kilka przypadków), tylko niekompetentny programista skorzystałby z autoakrementacji, a audytorzy są słusznie zdenerwowani.
HLGEM,

Odpowiedzi:


454

Wydaj następujące polecenie, aby ponownie załadować program mytable, aby rozpocząć od 1:

DBCC CHECKIDENT (mytable, RESEED, 0)

Przeczytaj o tym w Books on Line (BOL, pomoc SQL). Uważaj również, aby nie mieć rekordów wyższych niż ustawione ziarno.


4
... ponieważ identyfikatory tych rekordów zostaną ponownie wykorzystane, powodując zły bałagan.
dokładnie

3
W rzeczywistości, aby rozpocząć identyfikatory od 1, musisz użyć 0: DBCC CHECKIDENT (mytable, RESEED, 0)
Ryan Lundy

7
„DBCC CHECKIDENT (nazwa_tabeli)” ustawia ziarno na najwyższą tożsamość w tabeli, niż nie trzeba „uważać”
1027167

4
@ user1027167 Nie, twoja odpowiedź nie działała dla mnie. Inkrementował najwyższy zapisany identyfikator wewnętrzny. Musiałem wyraźnie użyć „RESEED, 18” w moim przypadku, aby uzyskać „19” jako kolejny identyfikator. Bez tego radośnie zwiększał „29”.
Matthis Kohli,

DBCC CHECKIDENT (nazwa_tabeli) zmienia ziarno tylko wtedy, gdy wartość tożsamości jest niższa niż maksymalna wartość w kolumnie. Jeśli więc wartość tożsamości jest już większa niż w przypadku @MatthisKohli, należy wywołać jawne ponowne ustawienie.
Martheen,

82
DBCC CHECKIDENT('databasename.dbo.tablename', RESEED, number)

jeśli liczba = 0, to w kolejnym wstawieniu pole automatycznego przyrostu będzie zawierało wartość 1

jeśli liczba = 101, to w kolejnym wstawieniu pole automatycznego przyrostu będzie zawierało wartość 102


Kilka dodatkowych informacji ... Może ci się przydać

Przed udzieleniem automatycznego przyrostu numberw powyższym zapytaniu musisz upewnić się, że kolumna automatycznego przyrostu w istniejącej tabeli zawiera wartości mniejsze niż number.

Aby uzyskać maksymalną wartość kolumny (nazwa_kolumny) z tabeli (tabela1), można użyć następującego zapytania

 SELECT MAX(column_name) FROM table1

37

pół-idiotoodporny:

declare @max int;  
select @max = max(key) from table;  
dbcc checkident(table,reseed,@max)

http://sqlserverplanet.com/tsql/using-dbcc-checkident-to-reseed-a-table-after-delete


1
„DBCC CHECKIDENT (nazwa_tabeli)” robi to samo (możliwe bez warunków wyścigu)
użytkownik1027167,

2
@ user1027167 Dokumenty mówią „jeśli bieżąca wartość tożsamości dla tabeli jest mniejsza niż maksymalna wartość tożsamości przechowywana w kolumnie tożsamości”; która nie obejmuje czyszczenia po usunięciu danych (ponowne użycie identyfikatorów - często zły pomysł). Zweryfikowano na SQL 2008
użytkownik423430,

1
Najlepsza systematyczna i automatyczna odpowiedź. Brawo!
Mehdi Khademloo,

11

Jeśli używasz MySQL, spróbuj tego:

ALTER TABLE tablename AUTO_INCREMENT = 1

3
To jest odpowiedź dla MySQL. OP pyta o MSSQL.
zreformowano

pytanie dotyczy MS SQL Server
Saher Ahwal

6

Usuń i ponownie posadź wszystkie tabele w bazie danych.

    USE [DatabaseName]
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"       -- Disable All the constraints
    EXEC sp_MSForEachTable "DELETE FROM ?"    -- Delete All the Table data
    Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 0)' -- Reseed All the table to 0
    Exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"  -- Enable All  the constraints back

-- You may ignore the errors that shows the table without Auto increment field.

6

Rozgryzłem to. Jego:

 DBCC CHECKIDENT ('tablename', RESEED, newseed)

4

Na podstawie zaakceptowanej odpowiedzi dla osób, które spotkały się z podobnym problemem, z pełną kwalifikacją schematu:

( [MyDataBase].[MySchemaName].[MyTable]) ... powoduje błąd, musisz znajdować się w kontekście tej bazy danych

Oznacza to, że następujący błąd zgłosi błąd:

DBCC CHECKIDENT ([MyDataBase].[MySchemaName].[MyTable], RESEED, 0)

Zamiast tego dołącz pełną nazwę tabeli do pojedynczych cudzysłowów:

DBCC CHECKIDENT ('[MyDataBase].[MySchemaName].[MyTable]', RESEED, 0)

4

Kilka odpowiedzi zaleca użycie takiego stwierdzenia:

DBCC CHECKIDENT (mytable, RESEED, 0)

Ale OP powiedział „usunął niektóre rekordy”, które mogą nie być wszystkie, więc wartość 0 nie zawsze jest właściwa. Inna odpowiedź sugeruje automatyczne znalezienie maksymalnej wartości prądu i ponowne wysunięcie do tej wartości, ale napotyka to problemy, jeśli nie ma żadnych rekordów w tabeli, a zatem max () zwróci NULL. Komentarz sugeruje użycie po prostu

DBCC CHECKIDENT (mytable)

zresetować wartość, ale inny komentarz poprawnie stwierdził, że zwiększa to wartość do maksimum już w tabeli; nie zmniejszy to wartości, jeśli jest już wyższa niż maksimum w tabeli, co chciał zrobić PO.

Lepsze rozwiązanie łączy te pomysły. Pierwszy CHECKIDENT resetuje wartość do 0, a drugi resetuje ją do najwyższej wartości aktualnie w tabeli, w przypadku gdy w tabeli znajdują się rekordy:

DBCC CHECKIDENT (mytable, RESEED, 0)
DBCC CHECKIDENT (mytable)

Jak wskazało wiele komentarzy, upewnij się, że w innych tabelach nie ma kluczy obcych wskazujących na usunięte rekordy. W przeciwnym razie te klucze obce wskażą rekordy utworzone po ponownym posadzeniu tabeli, co prawie na pewno nie jest tym, co miałeś na myśli.


4

Chcę dodać tę odpowiedź, ponieważ DBCC CHECKIDENT-podejście spowoduje problemy podczas używania schematów dla tabel. Użyj tego, aby mieć pewność:

DECLARE @Table AS NVARCHAR(500) = 'myschema.mytable';
DBCC CHECKIDENT (@Table, RESEED, 0);

Jeśli chcesz sprawdzić powodzenie operacji, użyj

SELECT IDENT_CURRENT(@Table);

które powinny wypisać się 0w powyższym przykładzie.


1

Ogólnie nie chcesz tego robić. Reseed może powodować problemy z integralnością danych. Jest tak naprawdę do użytku tylko w systemach programistycznych, w których usuwasz wszystkie dane testowe i zaczynasz od nowa. Nie należy go używać w systemie produkcyjnym, jeśli wszystkie powiązane rekordy nie zostały usunięte (nie każda tabela, która powinna być w relacji klucza obcego, jest!). Możesz stworzyć bałagan, robiąc to, a zwłaszcza jeśli chcesz to robić regularnie po każdym usunięciu. Nie warto martwić się lukami w wartościach pola tożsamości.


6
Nie będę go używać cały czas i było to tylko na testowej bazie danych.
jumbojs

0

A co z tym?

ALTER TABLE `table_name`
  MODIFY `id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0;

Jest to szybki i prosty sposób na zmianę automatycznego przyrostu na 0 lub dowolną żądaną liczbę. Zrozumiałem to, eksportując bazę danych i sam czytając kod.

Możesz również napisać to w ten sposób, aby uczynić z niego rozwiązanie jednowierszowe:

ALTER TABLE `table_name` MODIFY `id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=0;

1
Dziękujemy za ten fragment kodu, który może zapewnić ograniczoną, natychmiastową pomoc. Właściwe wyjaśnienie byłoby znacznie poprawić swoją długoterminową wartość pokazując dlaczego jest to dobre rozwiązanie problemu, a byłoby bardziej użyteczne dla czytelników przyszłości z innymi, podobnymi pytaniami. Proszę edytować swoją odpowiedź dodać kilka wyjaśnień, w tym założeń już wykonanych.
Do widzenia StackExchange
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.