Dodanie tożsamości do istniejącej kolumny


445

Muszę zmienić klucz podstawowy tabeli na kolumnę tożsamości, a tabela zawiera już wiele wierszy.

Mam skrypt do czyszczenia identyfikatorów, aby upewnić się, że są one sekwencyjne od 1, działa dobrze w mojej testowej bazie danych.

Jaka jest komenda SQL, aby zmienić kolumnę na właściwość tożsamości?

Odpowiedzi:


482

Nie można zmienić istniejących kolumn pod kątem tożsamości.

Masz 2 opcje,

  1. Utwórz nowy stół z tożsamością i upuść istniejący stół

  2. Utwórz nową kolumnę z tożsamością i upuść istniejącą kolumnę

Podejście 1. ( Nowa tabela ) Tutaj możesz zachować istniejące wartości danych w nowo utworzonej kolumnie tożsamości.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Sposób 2 ( nowa kolumna ) Nie można zachować istniejące wartości danych na kolumny tożsamości nowo utworzonej, Kolumna tożsamości odbędzie sekwencję numeru.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

Aby uzyskać więcej informacji, zobacz następujący post na forum Microsoft SQL Server:

Jak zmienić kolumnę na tożsamość (1,1)


49
Jeśli dane w tabeli są małe, ta opcja działa w trybie Gret. Jeśli tabela jest duża, wolę inną opcję: użyj ZMIEŃ TABELĘ ... PRZEŁĄCZ SWITCH, aby zastąpić schemat tabeli inną wersją z kolumną TOŻSAMOŚĆ, ale pod innym względem identyczny schemat. Zaletą metody ALTER TABLE .... SWITCH jest to, że wykonuje się ją szybko (poniżej 5 sekund dla tabeli o miliardach wierszy), ponieważ nie trzeba kopiować ani zmieniać danych tabeli. Istnieją jednak ograniczenia i ograniczenia. Zobacz moją odpowiedź poniżej, aby uzyskać szczegółowe informacje.
Justin Grant

7
@Justin Grat: Bardzo interesująca alternatywa, której nie rozważałem! Powodem tego jest fakt, że TOŻSAMOŚĆ jest właściwością kolumny, a nie typem danych, dlatego metoda SWITCH sprawdza poprawność schematów między dwiema tabelami (starą i nową) jako identyfikowalne niezależnie od różnicy TOŻSAMOŚCI. Dzięki za udostępnienie!
John Sansom,

Jeśli nie masz dużo danych, wówczas „utworzenie tabeli” można osiągnąć, generując skrypt z SSMS. Kliknij prawym przyciskiem myszy tabelę> Scrip Table as> Create TABLE to> (new editor query?). Następnie upuść go, a do tego skryptu możesz dodać IDENTITY(1, 1)część z kolumną klucza podstawowego
goamn

Można również użyć SSMS, aby to wymusić. Przejdź do Narzędzia> Opcje> Projektanci> Odznacz „Zapobiegaj zapisywaniu zmian wymagających ponownego utworzenia tabeli”. BTW nie jest to zalecane w przypadku dość dużych tabel.
Zafar

W PostgreSQL Państwo mogą dodać tożsamości do istniejącej kolumny całkowitej za pomocą polecenia: alter table {nazwa_tabeli} alter kolumny {column_name} Dodawanie generowane zawsze jako tożsamości (restart z {numer});
Andrew Mackie,

209

W SQL 2005 i nowszych istnieje sposób na rozwiązanie tego problemu bez zmiany stron danych tabeli. Jest to ważne w przypadku dużych tabel, w których dotknięcie każdej strony danych może zająć minuty lub godziny. Sztuczka działa także, nawet jeśli kolumna tożsamości jest kluczem podstawowym, jest częścią indeksu klastrowanego lub nieklastrowanego lub innych gotchas, które mogą wyzwalać prostsze rozwiązanie „dodaj / usuń / zmień nazwę kolumny”.

Oto sztuczka: możesz użyć instrukcji ALTER TABLE ... SQL SWITCH programu SQL Server, aby zmienić schemat tabeli bez zmiany danych, co oznacza, że ​​możesz zastąpić tabelę TOŻSAMOŚCIĄ o identycznym schemacie tabeli, ale bez kolumny TOŻSAMOŚCI. Ta sama sztuczka działa, aby dodać TOŻSAMOŚĆ do istniejącej kolumny.

Zwykle ALTER TABLE ... SWITCH służy do skutecznego zastąpienia pełnej partycji w podzielonej na partycje tabeli nową, pustą partycją. Ale może być również używany w niepodzielonych na partycje tabelach.

Użyłem tej sztuczki, aby w ciągu 5 sekund przekonwertować kolumnę tabeli o wartości 2,5 miliarda wierszy z IDENTITY na non-TOŻSAMOŚĆ (w celu uruchomienia wielogodzinnego zapytania, którego plan zapytań działał lepiej w przypadku braku tożsamości kolumny), a następnie przywrócono ustawienie TOŻSAMOŚĆ, ponownie w mniej niż 5 sekund.

Oto przykładowy kod tego, jak to działa.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

Jest to oczywiście bardziej zaangażowane niż rozwiązania w innych odpowiedziach, ale jeśli twój stół jest duży, może to być naprawdę uratowanie życia. Istnieje kilka zastrzeżeń:

  • O ile wiem, tożsamość jest jedyną rzeczą, którą możesz zmienić w kolumnach tabeli za pomocą tej metody. Dodawanie / usuwanie kolumn, zmiana nullability itp. Jest niedozwolone.
  • Będziesz musiał upuścić klucze foriegn przed wykonaniem przełączenia i przywrócić je później.
  • To samo dotyczy funkcji, widoków itp. Z SCHEMABINDINGEM
  • indeksy nowej tabeli muszą być dokładnie dopasowane (te same kolumny, ta sama kolejność itp.)
  • Stare i nowe tabele muszą znajdować się w tej samej grupie plików.
  • Działa tylko na SQL Server 2005 lub nowszym
  • Wcześniej uważałem, że ta sztuczka działa tylko w wersjach Enterprise lub Developer SQL Server (ponieważ partycje są obsługiwane tylko w wersjach Enterprise i Developer), ale Mason G. Zhwiti w swoim komentarzu poniżej mówi, że działa również w wersji SQL Standard Edition. Zakładam, że oznacza to, że ograniczenie do wersji Enterprise lub Developer nie dotyczy ALTER TABLE ... SWITCH.

Jest dobry artykuł na temat TechNet szczegółowo opisujący powyższe wymagania.

AKTUALIZACJA - Eric Wu miał poniżej komentarz, który dodaje ważne informacje o tym rozwiązaniu. Skopiuj go tutaj, aby mieć większą uwagę:

Jest jeszcze jedno zastrzeżenie, o którym warto wspomnieć. Chociaż nowa tabela z przyjemnością odbierze dane ze starej tabeli, a wszystkie nowe wiersze zostaną wstawione zgodnie ze wzorem tożsamości, zaczną od 1 i potencjalnie pękną, jeśli wspomniana kolumna jest kluczem podstawowym. Rozważ uruchomienie DBCC CHECKIDENT('<newTableName>')natychmiast po zmianie. Aby uzyskać więcej informacji, zobacz msdn.microsoft.com/en-us/library/ms176057.aspx .

Jeśli tabela jest aktywnie rozszerzana o nowe wiersze (co oznacza, że ​​nie masz dużo, jeśli masz jakiekolwiek przestoje między dodaniem TOŻSAMOŚCI a dodaniem nowych wierszy, to zamiast tego DBCC CHECKIDENTbędziesz chciał ręcznie ustawić wartość początkową tożsamości w nowym schemacie tabeli na większy niż największy istniejącego ID w tabeli, np IDENTITY (2435457, 1). może być w stanie uwzględnić zarówno ALTER TABLE...SWITCHa DBCC CHECKIDENTw transakcji (lub not-- nie testowałem tego), ale wydaje się, że ustawienie wartości początkowej ręcznie będzie łatwiejsze i bezpieczniejsze.

Oczywiście, jeśli żadne nowe wiersze nie są dodawane do tabeli (lub są dodawane tylko sporadycznie, jak codzienny proces ETL), to ten warunek wyścigu nie nastąpi, więc DBCC CHECKIDENTjest w porządku.


5
Jeśli moja pamięć jest poprawna, wpadłem na pomysł z tego artykułu: sqlservercentral.com/articles/T-SQL/61979
Justin Grant

2
Do Twojej wiadomości, wydaje się, że działa to również w Standardowej wersji SQL 2008 R2. Być może włączyli tę funkcję tak samo, jak włączyli kompresję kopii zapasowych.
Mason G. Zhwiti,

3
@jbatista - pytanie PO stwierdziło, że ma już klucz podstawowy na stole i może już zapewnić prawidłowe wartości, ale po prostu chciał go zmienić na kolumnę TOŻSAMOŚCI. Moja odpowiedź powyżej koncentruje się na wąskim przypadku użycia: jak dodać TOŻSAMOŚĆ do kolumny bez faktycznej zmiany jakichkolwiek danych. Podejście, które opisałem powyżej, to ogromna oszczędność czasu dla dużych stołów. Jeśli musisz zmienić dane, musisz użyć innych rozwiązań.
Justin Grant

3
Jest jeszcze jedno zastrzeżenie, o którym warto wspomnieć. Chociaż nowa tabela z przyjemnością odbierze dane ze starej tabeli, a wszystkie nowe wiersze zostaną wstawione zgodnie ze wzorem tożsamości, zaczną od 1 i potencjalnie pękną, jeśli wspomniana kolumna jest kluczem podstawowym. Rozważ uruchomienie DBCC CHECKIDENT('<newTableName>')natychmiast po zmianie. Aby uzyskać więcej informacji, zobacz msdn.microsoft.com/en-us/library/ms176057.aspx .
Eric Wu,

3
To świetna odpowiedź! Zauważ też, że zerowalność kolumn musi być taka sama. Więc jeśli musisz zmienić zerowanie kolumny, będziesz musiał to zrobić na późniejszym etapie. To samo dotyczy ograniczeń PK. Zmieniam również wartość tożsamości w tworzeniu tabeli, aby dopasować ją do bieżącego maksimum: TOŻSAMOŚĆ (maxID + 1, 1)
Philippe

71

Nie możesz zmienić kolumny na kolumnę TOŻSAMOŚCI. To, co musisz zrobić, to utworzyć nową kolumnę, która jest zdefiniowana jako TOŻSAMOŚĆ od samego początku, a następnie upuść starą kolumnę i zmień nazwę nowej na starą.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc


Albo parametr \ @objname jest niejednoznaczny, albo deklarowany \ @objtype (COLUMN) jest niepoprawny.
Jenny O'Reilly,

1
@ JennyO'Reilly: postaw to pytanie w osobnym pytaniu i pokaż nam kompletne polecenie, którego używasz!
marc_s

2
Nie powiodła się procedura sp_rename. Znalazłem rozwiązanie dotyczące przepływu stosu, szukając tekstu błędu. Wydaje się, że jest to ścisła reguła składniowa z nawiasami kwadratowymi, chociaż moja tabela nie ma żadnych znaków specjalnych w nazwie.
Jenny O'Reilly,

1
lub może być tak: „ALTER TABLE (twoTable) DROP COLUMN OldColumnName” i „ALTER TABLE (yourTable) DODAJ OldColumnName INT TOŻSAMOŚĆ (1,1)”, po co zmieniać nazwę: p
RK Sharma

Marc, wypróbowałem dokładnie to polecenie na ogromnym stole (~ 300mln wierszy), ale zatrzymałem proces po ~ 10 minutach
Naomi

14

Jest fajne rozwiązanie opisane tutaj: SERWER SQL - Dodaj lub usuń właściwość tożsamości w kolumnie

W skrócie ręcznie edytuj swoją tabelę w SQL Managerze, przełącz tożsamość, NIE ZAPISUJ zmian, po prostu pokaż skrypt, który zostanie utworzony dla zmian, skopiuj go i użyj go później.

Jest to ogromna oszczędność czasu, ponieważ (skrypt) zawiera wszystkie klucze obce, indeksy itp. Związane ze zmienianą tabelą. Pisanie tego ręcznie ... Boże broń.


to jest rozwiązanie, którego użyłem - SSMS generuje T-SQL, aby dokonać zmiany ... robi to poprzez utworzenie nowej tabeli tymczasowej o tym samym projekcie schematu, a następnie skopiowanie do niej wszystkich wierszy, usunięcie oryginału i zmianę nazwy . uruchomienie może trochę potrwać, ale działało idealnie.
mdelvecchio

Nie sądzę, żeby Pinal Dave powiedział, że musisz uruchomić skrypt, który wygenerowałeś, aby pokazać, co robi dla ciebie zmiana w interfejsie użytkownika ...
Zack

Ta łatwość obsługi skryptów w SSMS (przy zmianie definicji tabeli) jest w rzeczywistości jedynym właściwym narzędziem podczas dokumentowania partycjonowanej tabeli. Najodpowiedniejsze miejsce „zadanie” -> „tabela skryptów” zawsze zapomina o skryptowaniu funkcji partycjonowania!
Martijn van der Jagt

1
Może być komuś pomocny. Aby uzyskać skrypt zmian po zmianach. Kliknij prawym przyciskiem myszy tabelę w trybie projektowania w SSMS i wybierz opcję „Generuj skrypt zmian” i zapisz skrypt na dysku lokalnym
Vijai


6

Proste wyjaśnienie

Zmień nazwę istniejącej kolumny za pomocą sp_RENAME

EXEC sp_RENAME „Table_Name.Existing_ColumnName”, „New_ColumnName”, „COLUMN”

Przykład zmiany nazwy:

Nazwa istniejącej kolumny UserID zostaje zmieniona na OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Następnie dodaj nową kolumnę za pomocą zapytania alter, aby ustawić jako klucz podstawowy i wartość tożsamości

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Przykład ustawienia klucza podstawowego

Nowo utworzona nazwa kolumny to UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

następnie upuść nazwę kolumny o zmienionej nazwie

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Przykład dla kolumny o zmienionej nazwie

ALTER TABLE Users DROP COLUMN OldUserID

Teraz dodaliśmy klucz podstawowy i tożsamość do istniejącej kolumny w tabeli.


5

Jestem programistą Java, który akurat dostał się do zespołu bez DBA, a jako programista nie mogę uzyskać uprawnień DBA. Zadanie polegało na przeniesieniu całego schematu między dwiema bazami danych, więc bez DBA musiałem to zrobić, wykonując skrypty, nie mogąc korzystać z GUI w SQL Server 2008, ponieważ nie miałem uprawnień administratora.

Wszystko zostało przeniesione bez problemu, jednak podczas uruchamiania procedury składowanej na nowym schemacie. Schema.table odkryłem, że straciłem pole tożsamości w tabeli. Dokładnie sprawdziłem skrypt, który utworzył tabelę i tam był, jednak SQL Server nie dostał go, kiedy uruchomiłem skrypt. DBA powiedział mi później, że widział ten sam problem wcześniej.

W każdym razie, dla SQL Server 2008, są to kroki, które podjąłem, aby rozwiązać ten problem i zadziałały, więc zamieszczam to tutaj w nadziei, że będzie to komuś pomocne. To właśnie zrobiłem, ponieważ miałem zależności FK od innego stołu, co utrudniło to:

Użyłem tego zapytania, aby sprawdzić, czy rzeczywiście brakuje tożsamości i zobaczyć zależności od tabeli.

1.) Znajdź statystyki na stole:

exec sp_help 'dbo.table_name_old';

2.) Utwórz duplikat, identyczną nową tabelę, z wyjątkiem tego, że dodaj pole tożsamości w polu PK, w którym było wcześniej.

3.) Wyłącz tożsamość, aby przenieść dane.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Prześlij dane.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Sprawdź, czy dane tam są.

SELECT * FROM dbo.table_name_new

6.) Ponownie włącz tożsamość.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) To najlepszy skrypt, jaki znalazłem, aby uzyskać wszystkie relacje z FK, aby zweryfikować, która tabela (-y) w oryginalnej tabeli odwołuje się do zależności i natknąłem się na wiele, więc jest to opiekun!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Przed następnym krokiem upewnij się, że masz wszystkie skrypty PK i FK dla wszystkich zaangażowanych tabel.

9.) Możesz kliknąć prawym przyciskiem myszy każdy klucz i wykonać skrypt za pomocą SQL Server 2008

10.) Usuń FK z tabel zależności, używając tej składni:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Upuść oryginalny stół:

DROP TABLE dbo.table_name_old;

13.) Następne kroki opierają się na skryptach utworzonych w SQL Server 2008 w kroku 9.

- Dodaj PK do nowej tabeli.

- Dodaj FK do nowego stołu.

- Dodaj FK z powrotem do tabeli zależności.

14.) Sprawdź, czy wszystko jest poprawne i kompletne. Użyłem GUI do przeglądania tabel.

15.) Zmień nazwę nowej tabeli na pierwotną nazwę tabeli.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Wreszcie wszystko działało!


4

nie można tego zrobić w ten sposób, należy dodać kolejną kolumnę, upuścić oryginalną kolumnę i zmienić nazwę nowej kolumny lub utworzyć nową tabelę, skopiować dane i upuścić starą tabelę, a następnie zmienić jej nazwę na starą stół

jeśli używasz SSMS i ustawisz właściwość tożsamości na ON w projektancie, oto, co robi SQL Server za kulisami. Jeśli więc masz tabelę o nazwie [użytkownik], dzieje się tak, jeśli podasz identyfikator użytkownika i tożsamość

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Powiedziawszy, że istnieje sposób na zhakowanie tabeli systemowej, aby to osiągnąć, ustawiając wartość bitową, ale nie jest to obsługiwane i nie zrobiłbym tego


4

Jak rozumieć w normalnych przypadkach tworzymy tabelę z klucza podstawowego , który jest o właściwość Identity
So Zmień nazwę lub Usuń kolumnę, która jest związana z kluczem podstawowym ograniczeniem nie będzie możliwe, ponieważ zasady ograniczające potwierdzania strukturę kolumn.
Aby to osiągnąć, musimy wykonać kilka kroków w następujący sposób:
Załóżmy, że TableName = „Employee” i ColumnName = „EmployeeId”

1. Dodaj nową kolumnę „EmployeeId_new” w tabeli „Employee”
ALTER TABLE Pracownik ADD EmployeeId_new INT TOŻSAMOŚĆ ( 1,1)

  1. Teraz usuń kolumnę „EmployeeId” z tabeli „Employee”
    ALTER TABLE Pracownik DROP COLUMN EmployeeId

  2. Spowoduje to wygenerowanie błędu, ponieważ obowiązują reguły ograniczeń klucza głównego i sprawdzanie poprawności struktury kolumny.
    * ### ' Msg 5074, poziom 16, stan 1, wiersz 1 Obiekt [PK_dbo.Employee] jest zależny od colmn [EmployeeId]. ” ###

  3. Musimy więc najpierw usunąć ograniczenie klucza podstawowego z tabeli „Pracownik”, a następnie możemy usunąć kolumnę
    ALTER TABLE Ograniczenie DROP pracownika [PK_dbo.Employee]

  4. Teraz możemy usunąć kolumnę „EmployeeId” z tabeli „Employee”, tak jak w poprzednim kroku, w którym wystąpił błąd
    ALTER TABLE Pracownik DROP COLUMN EmployeeId

  5. Teraz kolumna „EmployeeId” usunięta z tabeli Więc zmienimy nazwę nowo dodanej nowej kolumny „EmployeeId_new” na „EmployeeId”
    sp_rename „Employee.EmployeeId”, „EmployeeId_new”, „COLUMN”

  6. Aby zmienić układ tabeli w takiej samej formie, w jakiej była, musimy dodać Ograniczenie klucza głównego dla kolumny „EmployeeId”
    ALTER TABLE Pracownik dodać ograniczenie [PK_dbo.Employee] klucz podstawowy (EmployeeId)

8. Teraz tabela „Pracownik” z „EmployeeId” została zmodyfikowana dla reguł tożsamości wraz z istniejącym ograniczeniem klucza podstawowego


3

Z założenia nie ma prostego sposobu włączenia lub wyłączenia funkcji tożsamości dla istniejącej kolumny. Jedynym czystym sposobem na to jest utworzenie nowej kolumny i uczynienie z niej kolumny tożsamości lub utworzenie nowej tabeli i migracja danych.

Jeśli użyjemy SQL Server Management Studio, aby pozbyć się wartości tożsamości w kolumnie „id”, tworzona jest nowa tabela tymczasowa, dane są przenoszone do tabeli tymczasowej, stara tabela jest usuwana, a nowa tabela jest zmieniana.

Użyj Management Studio, aby wprowadzić zmiany, a następnie kliknij projektanta prawym przyciskiem myszy i wybierz „Generuj skrypt zmian”.

Zobaczysz, że to właśnie robi serwer SQL w tle.


2

Niestety nie ma jednego; właściwość TOŻSAMOŚĆ należy raczej do tabeli niż do kolumny.

Najprostszym sposobem jest zrobienie tego w GUI, ale jeśli nie jest to opcja, możesz przejść długą drogę kopiowania danych, upuszczania kolumny, dodawania jej z tożsamością i przywracania danych.

Zobacz tutaj konto blow-by-blow.


2

Kliknij prawym przyciskiem myszy nazwę tabeli w Eksploratorze obiektów. Dostaniesz kilka opcji. Kliknij „Projekt”. Zostanie otwarta nowa karta dla tej tabeli. Możesz dodać ograniczenie tożsamości tutaj w „Właściwościach kolumny”.


2

Aby zmodyfikować właściwości tożsamości dla kolumny:

  • W Eksploratorze serwera kliknij prawym przyciskiem myszy tabelę z właściwościami tożsamości, które chcesz zmodyfikować, a następnie kliknij polecenie Otwórz definicję tabeli. Tabela zostanie otwarta w Projektancie tabel.
  • Wyczyść pole wyboru Zezwalaj na wartości zerowe dla kolumny, którą chcesz zmienić.
  • Na karcie Właściwości kolumny rozwiń właściwość Specyfikacja tożsamości.
  • Kliknij komórkę siatki dla właściwości potomnej Is Identity i wybierz Tak z rozwijanej listy.
  • Wpisz wartość w komórce Początek tożsamości. Ta wartość zostanie przypisana do pierwszego wiersza w tabeli. Wartość 1 zostanie przypisana domyślnie.

To wszystko i zadziałało dla mnie


2

Jeśli akurat używasz programu Visual Studio 2017+

  1. W Server Object Explorer kliknij prawym przyciskiem myszy tabelę i wybierz „wyświetl kod”
  2. Dodaj modyfikator „TOŻSAMOŚĆ” do kolumny
  3. Aktualizacja

To wszystko dla ciebie zrobi.


Tak! Dziękujemy za sugestię! Nie mam wersji SSMS na moim pudełku Windows 7, który pozwala mi dokonywać zmian w projektach tabel na moim serwerze produkcyjnym, ponieważ jest to 2017, mój SSMS to 2014, a 2017 SSMS potrzebuje Windows 10. Zrobiłeś mój dzień. Udałem się do VS 2017> Server Explorer> nawiązałem nowe połączenie z produkcyjnym serwerem SQL> kliknij prawym przyciskiem myszy tabelę> „Definicja tabeli otwartej”> Wala!
JustJohn

W rzeczywistości stwierdziłem, że możesz kliknąć prawym przyciskiem myszy pole i wybrać Właściwości i dokonać tam tożsamości, wybierając Tak lub Nie
JustJohn

1

Jeśli oryginalny plakat naprawdę chciał ustawić istniejącą kolumnę PRIMARY KEYna tabelę i faktycznie nie potrzebował kolumny jako IDENTITYkolumny (dwie różne rzeczy), można to zrobić za pomocą t-SQL za pomocą:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

Zwróć uwagę na nawias wokół nazwy kolumny po PRIMARY KEYopcji.

Chociaż ten post jest stary i zakładam, że potrzeby osób żądających, uważam, że te dodatkowe informacje mogą być pomocne dla użytkowników napotykających ten wątek, ponieważ uważam, że rozmowa może prowadzić do przekonania, że ​​istniejącej kolumny nie można ustawić jako klucz podstawowy bez dodawania go jako nowej kolumny, co byłoby niepoprawne.


1

Zgodnie z moim obecnym stanem stosuję to podejście. Chcę nadać tożsamość tabeli podstawowej po danych wstawionych za pomocą skryptu.

Ponieważ chcę dołączyć tożsamość, zawsze zaczynam od 1 do końca rekordu, który chcę.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

Spowoduje to utworzenie tej samej kolumny klucza podstawowego z tożsamością

Użyłem tego linku: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Dodaj klucz podstawowy do istniejącej tabeli


0

Nie sądzę, że można zmienić istniejącą kolumnę na kolumnę tożsamości za pomocą tsql. Można to jednak zrobić w widoku projektu Enterprise Manager.

Alternatywnie możesz utworzyć nowy wiersz jako kolumnę tożsamości, upuścić starą kolumnę, a następnie zmienić nazwę nowej kolumny.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

2
pamiętaj, że jeśli zrobisz to przez SSMS / Enterprise Manager - będziesz tworzyć nową tabelę, kopiować dane, upuszczać starą tabelę i zmieniać jej nazwę. To może być dość drogie, gdy masz duże stoły ...
Scott Ivey

0

Zasadniczo istnieją cztery logiczne kroki.

  1. Utwórz nową kolumnę Tożsamość. Włącz Wstaw tożsamość dla tej nowej kolumny.

  2. Wstaw dane z kolumny źródłowej (kolumny, którą chcesz przekonwertować na Tożsamość) do tej nowej kolumny.

  3. Wyłącz wstawianie tożsamości dla nowej kolumny.

  4. Upuść kolumnę źródłową i zmień nazwę nowej kolumny na nazwę kolumny źródłowej.

Mogą występować dodatkowe trudności, takie jak praca na wielu serwerach itp.

Zapoznaj się z poniższym artykułem, aby uzyskać instrukcje (przy użyciu ssms i T-sql). Te kroki są przeznaczone dla początkujących z mniejszą znajomością T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx


0

generuje skrypt dla wszystkich tabel z kluczem podstawowym = bigint, które nie mają zestawu tożsamości; to zwróci listę wygenerowanych skryptów z każdą tabelą;

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where id=@@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
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.