Hinzufügen einer Identität zu einer vorhandenen Spalte

Question

Ich muss den Primärschlüssel einer Tabelle in eine Identitätsspalte ändern, und es gibt bereits eine Reihe von Zeilen in der Tabelle.

Ich habe ein Skript, um die IDs zu bereinigen, um sicherzustellen, dass sie bei 1 beginnend sequentiell sind, funktioniert gut auf meine Testdatenbank.

Wie lautet der SQL-Befehl, um die Spalte so zu ändern, dass sie eine Identitätseigenschaft hat?

Answer 1

Einfache Erklärung

Umbenennen der vorhandenen Spalte mit sp_RENAME

EXEC sp_RENAME 'Tabelle_Name.Bestehender_Spaltenname' , 'Neuer_Spaltenname', 'SPALTE'

Beispiel für Umbenennen :

Die bestehende Spalte UserID wird umbenannt in OldUserID

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

Fügen Sie dann eine neue Spalte mit alter query als Primärschlüssel und Identitätswert hinzu

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

Beispiel für Primärschlüssel setzen

Der Name der neu erstellten Spalte lautet UserID

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

dann die umbenannte Spalte löschen

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Beispiel für Drop umbenannte Spalte

ALTER TABLE Users DROP COLUMN OldUserID

Jetzt fügen wir einen Primärschlüssel und eine Identität zu der vorhandenen Spalte in der Tabelle hinzu.

Answer 2

So wie ich es verstanden habe, erstellen wir in normalen Fällen eine Tabelle mit Primärschlüssel die Eigenschaft "Identität
Also Umbenennen o Löschen eine Spalte, die verbunden ist mit Primärschlüssel Einschränkung ist nicht möglich, da Constraint Rules die Spaltenstruktur validieren.
Um dies zu erreichen, müssen wir einige Schritte auf die folgende Weise durchführen:
Nehmen wir an Tabellenname = 'Mitarbeiter' y Spaltenname = 'EmployeeId'

1. Hinzufügen einer neuen Spalte "EmployeeId_new" in der Tabelle "Employee".
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

2. Entfernen Sie nun die Spalte 'EmployeeId' aus der Tabelle 'Employee'.
   ALTER TABLE Employee DROP COLUMN EmployeeId

3. Dies führt zu einem Fehler, da die Regeln für die Primärschlüssel-Beschränkung gelten und die Spaltenstruktur validiert wird.
   *### ' Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].' ###

4. Wir müssen also zuerst die Primärschlüssel-Beschränkung aus der Tabelle "Employee" entfernen, dann können wir die Spalte
   ALTER TABLE Employee DROP constraint [PK_dbo.Employee]

5. Jetzt können wir die Spalte "EmployeeId" aus der Tabelle "Employee" entfernen, wie im vorherigen Schritt, bei dem wir einen Fehler erhielten
   ALTER TABLE Employee DROP COLUMN EmployeeId

6. Jetzt wird die Spalte "EmployeeId" aus der Tabelle entfernt. Wir werden also die neu hinzugefügte Spalte 'EmployeeId_new' in 'EmployeeId' umbenennen
   sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

7. Um die Tabelle wieder so anzuordnen, wie sie vorher war, müssen wir für die Spalte "EmployeeId" eine Primärschlüssel-Beschränkung hinzufügen
   ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

8. Jetzt wird die Tabelle 'Employee' mit 'EmployeeId' für Identitätsregeln zusammen mit der bestehenden Primärschlüssel-Beschränkung geändert

Answer 3

Ich bin ein Java-Entwickler, der zufällig in ein Team ohne DBA gekommen ist und in dem ich als Entwickler keine DBA-Rechte bekommen kann. Ich hatte die Aufgabe, ein komplettes Schema zwischen zwei Datenbanken zu verschieben. Da ich keinen DBA hatte, musste ich das mit Hilfe von Skripten machen und konnte die grafische Benutzeroberfläche von SQL Server 2008 nicht verwenden, weil ich keine Admin-Rechte hatte.

Alles wurde ohne Probleme verschoben, aber beim Ausführen einer gespeicherten Prozedur in der neuen schema.table stellte ich fest, dass ich das Identitätsfeld in einer Tabelle verloren hatte. Ich überprüfte das Skript, mit dem die Tabelle erstellt wurde, und es war vorhanden, aber SQL Server konnte es nicht abrufen, als ich das Skript ausführte. Später erfuhr ich von einem DBA, dass er dasselbe Problem schon einmal erlebt hatte.

Auf jeden Fall sind dies die Schritte, die ich für SQL Server 2008 unternommen habe, um das Problem zu beheben, und sie haben funktioniert, also poste ich das hier in der Hoffnung, dass es jemandem hilft. Ich bin folgendermaßen vorgegangen, da ich FK-Abhängigkeiten zu einer anderen Tabelle hatte, die das Problem erschwerten:

Ich habe diese Abfrage verwendet, um zu überprüfen, ob die Identität tatsächlich fehlt, und um die Abhängigkeiten der Tabelle anzuzeigen.

1.) Finden Sie Statistiken in einer Tabelle:

exec sp_help 'dbo.table_name_old';

2.) Erstellen Sie eine doppelte, identische neue Tabelle, fügen Sie jedoch ein Identitätsfeld an der Stelle des PK-Feldes ein, an der es sich zuvor befunden hat.

3.) Deaktivieren Sie die Identität, um Daten zu verschieben.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Übertragen Sie die Daten.

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

5.) Überprüfen Sie, ob die Daten vorhanden sind.

SELECT * FROM dbo.table_name_new

6.) Aktivieren Sie die Identität erneut.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) Dies ist das beste Skript, das ich gefunden habe, um alle FK-Beziehungen zu erhalten und zu überprüfen, welche Tabelle(n) die Originaltabelle als Abhängigkeiten referenziert und ich bin auf viele gestoßen, daher ist es ein Muss!

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.) Vergewissern Sie sich, dass Sie alle PK- und FK-Skripte für alle beteiligten Tabellen haben, bevor Sie diesen nächsten Schritt ausführen.

9.) Sie können mit der rechten Maustaste auf jeden Schlüssel klicken und dieses Skript mit SQL Server 2008

10.) Entfernen Sie den/die FK aus der/den Abhängigkeitstabelle(n) unter Verwendung dieser Syntax:

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

11.) Lassen Sie die ursprüngliche Tabelle fallen:

DROP TABLE dbo.table_name_old;

13.) Diese nächsten Schritte beruhen auf den Skripts, die Sie in Schritt 9 in SQL Server 2008 erstellt haben.

-Hinzufügen der PK zur neuen Tabelle.

-Hinzufügen der FK in die neue Tabelle.

-Addieren Sie die FK's wieder in die Abhängigkeitstabelle.

14.) Überprüfen Sie, ob alles korrekt und vollständig ist. Ich habe die GUI benutzt, um mir die Tabellen anzusehen.

15.) Benennen Sie die neue Tabelle in den Namen der Originaltabelle um.

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

Endlich hat alles funktioniert!

Answer 4

Wenn Sie Visual Studio 2017+ verwenden

1. Klicken Sie im Server Object Explorer mit der rechten Maustaste auf Ihre Tabelle und wählen Sie "Code anzeigen".
2. Fügen Sie den Modifikator "IDENTITY" zu Ihrer Spalte hinzu
3. Update

Dies wird alles für Sie tun.

Answer 5

Sie müssen eine weitere Spalte hinzufügen, die ursprüngliche Spalte löschen und die neue Spalte umbenennen oder eine neue Tabelle erstellen, die Daten hineinkopieren, die alte Tabelle löschen und dann die neue Tabelle in die alte Tabelle umbenennen.

Wenn Sie SSMS verwenden und die Identitätseigenschaft im Designer auf ON setzen, zeigt sich, was SQL Server hinter den Kulissen tut. Wenn Sie also eine Tabelle mit dem Namen [user] haben, passiert Folgendes, wenn Sie UserID und identity zu

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

Abgesehen davon gibt es eine Möglichkeit, die Systemtabelle zu hacken, um dies zu erreichen, indem man den bitweisen Wert setzt, aber das wird nicht unterstützt und ich würde es nicht tun