Вопрос по sql-server, sql – Добавление идентификатора в существующий столбец

383

Мне нужно изменить первичный ключ таблицы на столбец идентификаторов, и в таблице уже есть ряд строк.

У меня есть скрипт для очистки идентификаторов, чтобы гарантировать, что они последовательно начинаются с 1, и отлично работают в моей тестовой базе данных.

Что такое команда SQL, чтобы изменить столбец, чтобы иметь свойство идентификации?

Ваш Ответ

19   ответов
3

ию идентификации для существующего столбца. Единственный чистый способ сделать это - создать новый столбец и сделать его столбцом идентификаторов или создать новую таблицу и перенести ваши данные.

Если мы используем SQL Server Management Studio, чтобы избавиться от значения идентификатора в столбце «id», создается новая временная таблица, данные перемещаются во временную таблицу, старая таблица удаляется, а новая таблица переименовывается.

Используйте Management Studio, чтобы внести изменения, а затем щелкните правой кнопкой мыши в конструкторе и выберите «Создать сценарий изменения».

Вы увидите, что это то, что SQL-сервер делает в фоновом режиме.

0

In Server Object Explorer right-click on your table and select "view code" Add the modifier "IDENTITY" to your column Update

Это сделает все для вас.

6

Simple explanation

Переименуйте существующий столбец, используя sp_RENAME

EXEC sp_RENAME & Table_Name. Existing_ColumnName & apos; , "New_ColumnName", "COLUMN";

Example for Rename :

Существующий столбец UserID переименован в OldUserID

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

Затем добавьте новый столбец, используя запрос alter для установки в качестве первичного ключа и значения идентификатора.

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

Example for Set Primary key

Имя нового созданного столбца - UserID

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

затем бросьте переименованную колонку

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Example for Drop renamed column

ALTER TABLE Users DROP COLUMN OldUserID

Теперь мы добавляем первичный ключ и идентификатор в существующий столбец таблицы.

0

Create a new Identity column. Turn on Insert Identity for this new column.

Insert the data from the source column (the column you wished to convert to Identity) to this new column.

Turn off the Insert Identity for the new column.

Drop your source column & rename the new column to the name of the source column.

Там могут быть некоторые сложности, такие как работа на нескольких серверах и т. Д.

Пожалуйста, обратитесь к следующей статье за шагами (используя ssms & T-sql). Эти шаги предназначены для начинающих с меньшим контролем T-SQL.

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

2

олучите несколько вариантов. Нажмите на «Дизайн». Новая таблица будет открыта для этой таблицы. Вы можете добавить ограничение идентичности здесь в разделе «Свойства столбца».

420

У вас есть 2 варианта,

Create a new table with identity & drop the existing table

Create a new column with identity & drop the existing column

Подход 1. (New table) Здесь вы можете сохранить существующие значения данных во вновь созданном столбце идентификаторов.

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'

Подход 2 (New column) Вы не можете сохранить существующие значения данных во вновь созданном столбце идентификаторов. В столбце идентификаторов будет содержаться последовательность чисел.

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'

Для получения дополнительной информации см. Следующий пост на форуме по Microsoft SQL Server:

Как изменить столбец на тождество (1,1)

@Justin Grat: очень интересная альтернатива, которую я не рассматривал! Это работает потому, что IDENTITY является свойством столбца, а не типом данных, поэтому метод SWITCH проверяет схемы между двумя таблицами (старой и новой) как идентифицируемые независимо от разницы IDENTITY. Спасибо, что поделился!
Можно также использовать SSMS для обеспечения этого. Перейдите в Инструменты & gt; Параметры & gt; Проектировщики & GT; Снимите флажок & quot; Запретить сохранение изменений, которые требуют пересоздания таблицы & quot ;. Кстати, это не рекомендуется для довольно больших таблиц.
В PostgreSQL выcan добавьте идентификатор в существующий целочисленный столбец с помощью команды: alter table {имя_таблицы} alter column {имя_столбца} добавьте сгенерированный всегда как идентификатор (перезапустите с {число});
Если у вас нет большого количества данных, тогда «создайте таблицу». может быть достигнуто путем генерации скрипта из SSMS. Нажмите правой кнопкой мыши на таблицу & gt; Таблица сценариев как & gt; Создать таблицу для & gt; (новый редактор запросов?). Затем бросьте его, и внутри этого скрипта вы можете добавитьIDENTITY(1, 1) часть с колонкой первичного ключа
Если данные таблицы малы, эта опция работает gret. Если таблица большая, есть другой вариант, который я предпочитаю: использовать ALTER TABLE ... SWITCH, чтобы заменить схему таблицы другой версией со столбцом IDENTITY, но в остальном идентичной схемой. Преимущество подхода ALTER TABLE .... SWITCH заключается в том, что он выполняется быстро (менее 5 секунд для таблицы с миллиардами строк), поскольку данные таблицы не нужно копировать или изменять. Есть предостережения и ограничения, хотя. Смотрите мой ответ ниже для деталей.
1

столбец какPRIMARY KEY для таблицы и на самом деле не нужен столбец, чтобы бытьIDENTITY столбец (две разные вещи), то это можно сделать через t-SQL с помощью:

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

Обратите внимание на круглые скобки вокруг имени столбца послеPRIMARY KEY вариант.

Хотя этот пост старый, и я делаю предположение о необходимости запрашивающих, я чувствовал, что эта дополнительная информация может быть полезна для пользователей, сталкивающихся с этой веткой, так как я считаю, что разговор может привести к тому, что существующий столбец не может быть установлен как первичный ключ без добавления его в качестве нового столбца, что будет неправильно.

3

вам нужно добавить другой столбец, удалить исходный столбец и переименовать новый столбец или создать новую таблицу, скопировать данные и удалить старую таблицу, а затем переименовать новую таблицу в старую. Таблица

если вы используете SSMS и устанавливаете свойство идентификатора на ON в конструкторе, то вот что SQL Server делает за кулисами. Так что если у вас есть таблица с именем [user], это то, что происходит, если вы делаете UserID и идентификатор

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

Сказав, что есть способ взломать системную таблицу, чтобы выполнить это, установив побитовое значение, но это не поддерживается, и я бы не стал это делать

0

у которых нет набора идентификаторов; это вернет список сгенерированных скриптов с каждой таблицей;

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  '[email protected]+' FROM ['[email protected]_schema+'].['[email protected]_name+'] ORDER BY '[email protected]+' 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 ['[email protected]_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 [email protected]@identity

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

insert into @sql(s) values( 'SET IDENTITY_INSERT ['[email protected]_schema+'].['[email protected]_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 ['[email protected]_schema+'].['[email protected]_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['[email protected]_schema+'].['[email protected]_name+'_Temp] ('[email protected]+')')
insert into @sql(s) values( 'SELECT '[email protected]+' FROM ['[email protected]_schema+'].['[email protected]_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['[email protected]_schema+'].['[email protected]_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['[email protected]_schema+'].['[email protected]_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['[email protected]_schema+'].['[email protected]_name+'_Temp]'', N'''[email protected]_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['[email protected]_schema+'].['[email protected]_name+'] ADD CONSTRAINT ['[email protected]+'] 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 [email protected]@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: '[email protected]_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
180

страниц данных таблицы. Это важно для больших таблиц, где прикосновение к каждой странице данных может занять минуты или часы. Трюк также работает, даже если столбец идентификаторов является первичным ключом, является частью кластеризованного или некластеризованного индекса или других ошибок, которые могут привести к отключению более простого столбца «добавить / удалить / переименовать». решение.

Вот хитрость: вы можете использовать SQL ServerALTER TABLE ... ВЫКЛЮЧАТЕЛЬ оператор для изменения схемы таблицы без изменения данных. Это означает, что вы можете заменить таблицу с IDENTITY идентичной схемой таблицы, но без столбца IDENTITY. Тот же трюк работает для добавления IDENTITY в существующий столбец.

Обычно,ALTER TABLE ... ВЫКЛЮЧАТЕЛЬ используется для эффективной замены полного раздела в многораздельной таблице новым пустым разделом. Но его также можно использовать и в однораздельных таблицах.

Я использовал этот трюк для преобразования менее чем за 5 секунд столбца таблицы из 2,5 миллиардов строк из IDENTITY в не-IDENTITY (для того, чтобы выполнить многочасовой запрос, план запроса которого работал лучше для не-IDENTITY столбцы), а затем восстановил настройку IDENTITY, снова менее чем за 5 секунд.

Вот пример кода того, как это работает.

 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; 

Это, очевидно, более сложное решение, чем в других ответах, но если у вас большой стол, это может спасти жизнь. Есть несколько предостережений:

As far as I know, identity is the only thing you can change about your table's columns with this method. Adding/removing columns, changing nullability, etc. isn't allowed. You'll need to drop foriegn keys before you do the switch and restore them after. Same for WITH SCHEMABINDING functions, views, etc. new table's indexes need to match exactly (same columns, same order, etc.) Old and new tables need to be on the same filegroup. Only works on SQL Server 2005 or later I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.

Это хорошостатья на TechNet детализация требований выше.

ОБНОВИТЬ -Эрик Ву был комментарий ниже, который добавляет важную информацию об этом решении. Скопируйте его сюда, чтобы убедиться, что он привлекает больше внимания:

There's another caveat here that is worth mentioning. Althought the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('<newTableName>') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.

Если таблица активно расширяется новыми строками (то есть у вас не будет большого количества простоев между добавлением IDENTITY и добавлением новых строк, вместоDBCC CHECKIDENT Вы захотите вручную установить начальное значение идентификатора в новой схеме таблицы так, чтобы оно было больше, чем самый большой существующий идентификатор в таблице, например,IDENTITY (2435457, 1), Вы можете включить какALTER TABLE...SWITCH иDBCC CHECKIDENT в транзакции (или нет - это не проверялось), но кажется, что установка начального значения вручную будет проще и безопаснее.

Очевидно, что если в таблицу не добавляются новые строки (или они добавляются только изредка, как ежедневный процесс ETL), то это условие гонки не произойдет, так чтоDBCC CHECKIDENT Это хорошо.

Если моя память верна, я получил идею из этой статьи:sqlservercentral.com/articles/T-SQL/61979
К вашему сведению, это также работает в стандартной версии SQL 2008 R2. Возможно, они включили эту функцию точно так же, как они теперь включили возможность включения сжатия резервной копии.
Мейсон - отличное наблюдение! Я обновил свой ответ соответственно. Я предположил, что, поскольку в стандарте SQL разделы не поддерживаются, ALTER TABLE ... SWITCH также не будет поддерживаться. Но, учитывая, что вы можете использовать эту команду для однораздельных таблиц, имеет смысл, что вы также можете запустить ее в Standard Edition. Спасибо за исправление.
@jbatista - в вопросе OP указано, что у него уже есть первичный ключ в таблице и он уже может обеспечить правильные значения, но он просто хотел изменить его на столбец IDENTITY. Мой ответ выше сфокусирован на том узком сценарии использования: как добавить IDENTITY в столбец без фактического изменения каких-либо данных. Подход, который я описал выше, значительно экономит время для больших таблиц. Если вам нужно изменить данные, вам нужно будет использовать другие решения.
Здесь следует упомянуть еще одну оговорку. Хотя новая таблица с радостью получит данные из старой таблицы, и все новые строки будут вставлены в соответствии с шаблоном идентификации,they will start at 1 и потенциально сломаться, если указанный столбец является первичным ключом. Рассмотрим бегDBCC CHECKIDENT('<newTableName>') сразу после переключения. Увидетьmsdn.microsoft.com/en-us/library/ms176057.aspx для получения дополнительной информации.
4

который попал в команду без администратора и в которой я, как разработчик, не могу получить права администратора. Мне было поручено переместить всю схему между двумя базами данных, поэтому без администратора базы данных мне пришлось делать это и делать это с помощью сценариев, поскольку я не мог использовать графический интерфейс пользователя в SQL Server 2008, поскольку у меня не было прав администратора.

Все было перенесено без проблем, однако при запуске хранимой процедуры в новой schema.table я обнаружил, что потерял поле идентификатора в таблице. Я дважды проверил скрипт, который создал таблицу, и он был там, однако, SQL Server не получил его, когда я запустил скрипт. Позже мне сказал администратор базы данных, что он видел эту же проблему раньше.

В любом случае, для SQL Server 2008 это были шаги, которые я предпринял, чтобы решить эту проблему, и они сработали, поэтому я публикую это здесь в надежде, что это кому-нибудь поможет. Это то, что я сделал, поскольку у меня были зависимости FK от другой таблицы, что усложняло это:

Я использовал этот запрос, чтобы убедиться, что идентификация действительно отсутствует, и просмотреть зависимости от таблицы.

1.) Найти статистику по таблице:

exec sp_help 'dbo.table_name_old';

2.) Создайте дубликат идентичной новой таблицы, за исключением того, что добавьте поле идентификатора в поле PK, где оно было раньше.

3.) Отключить удостоверение для перемещения данных.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Передача данных.

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

5.) Убедитесь, что данные есть.

SELECT * FROM dbo.table_name_new

6.) Повторно включите личность.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) This is the best script I found to get all the FK relationships to verify which table(s) the original table references as dependencies and I came across many, so it is a keeper!

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.) Перед следующим шагом убедитесь, что у вас есть все сценарии PK и FK для всех задействованных таблиц.

9.) Вы можете щелкнуть правой кнопкой мыши по каждому ключу и написать скрипт, используя SQL Server 2008

10.) Удалите FK из таблицы зависимостей, используя этот синтаксис:

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

11.) Оставьте оригинальный стол:

DROP TABLE dbo.table_name_old;

13.) Следующие шаги основаны на сценариях, которые вы создали в SQL Server 2008 на шаге 9.

- Добавить ПК к новой таблице.

- Добавить FK в новую таблицу.

- добавить FK обратно в таблицу зависимостей.

14.) Убедитесь, что все правильно и полно. Я использовал графический интерфейс для просмотра таблиц.

15.) Переименуйте новую таблицу в исходное имя таблицы.

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

Наконец-то все заработало!

62

ать, это создать новый столбец, который определяется как IDENTITY с самого начала, затем отбросить старый столбец и переименовать новый в старое имя.

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

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

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

Марк

или это может быть похоже на: «ALTER TABLE (yourTable) DROP COLUMN OldColumnName & apos; и «ALTER TABLE (yourTable) ДОБАВИТЬ OldColumnName INT IDENTITY (1,1)», зачем переименовывать: p
Это не работает, если этот столбец является ограничением
@ JennyO 'Reilly: разбери это в отдельный вопрос иshow us полная команда, которую вы используете!
Либо параметр \ @objname является неоднозначным, либо заявленный \ @objtype (COLUMN) неверен.
Это была процедура sp_rename, которая терпела неудачу. Я нашел решение для stackoverflow путем поиска текста ошибки. Кажется, это строгое правило синтаксиса с квадратными скобками, хотя в моей таблице нет никаких специальных символов в названии.
2

свойство IDENTITY принадлежит таблице, а не столбцу.

Проще всего сделать это в графическом интерфейсе, но если это не вариант, вы можете пройти долгий путь, копируя данные, отбрасывая столбец, повторно добавляя его с идентификатором и возвращая данные.

УвидетьВот  за счет за ударом.

1

я следую этому подходу. Я хочу, чтобы идентифицировать первичную таблицу после данных, вставленных через скрипт.

Как я хочу добавить идентичность, так что она всегда начинается с 1 до конца количества записей, которые я хочу.

--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

Это создаст тот же столбец первичного ключа с идентичностью

Я использовал эту ссылку:https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Добавить первичный ключ в существующую таблицу

0

что вы можете изменить существующий столбец как столбец идентификаторов, используя tsql. Однако это можно сделать с помощью представления конструктора Enterprise Manager.

В качестве альтернативы вы можете создать новую строку в качестве столбца идентификаторов, удалить старый столбец, а затем переименовать новый столбец.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED
помните, что если вы сделаете это через SSMS / Enterprise Manager - вы будете создавать новую таблицу, копировать данные, удалять старую таблицу и переименовывать новую. Это может быть довольно дорого, если у вас большие столы ...
2

In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer. Clear the Allow nulls check box for the column you want to change. In the Column Properties tab, expand the Identity Specification property. Click the grid cell for the Is Identity child property and choose Yes from the drop-down list. Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.

Вот и все, и это сработало для меня

6

ПОСЛЕДОВАТЕЛЬНОСТЬ вместо ИДЕНТИЧНОСТИ.

В SQL Server 2014 (я не знаю о более низких версиях) вы можете сделать это просто, используя последовательность.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

Отсюда:Последовательность в качестве значения по умолчанию для столбца

14

SQL SERVER & # x2013; Добавить или удалить свойство идентичности в столбце

Короче говоря, отредактируйте вручную таблицу в SQL Manager, поменяйте личность, НЕ СОХРАНЯЙТЕ изменения, просто покажите сценарий, который будет создан для изменений, скопируйте его и используйте позже.

Это значительно экономит время, потому что он (скрипт) содержит все внешние ключи, индексы и т. Д., Относящиеся к изменяемой вами таблице. Пишу это вручную ... не дай бог.

Эта возможность сценариев в SSMS (при изменении определения таблицы) фактически является единственно правильным средством документирования многораздельной таблицы. Наиболее подходящее местоположение «задача» - «таблица сценариев»; всегда забудьте сценарий функции разделения!
Я не думаю, что Пинал Дейв на самом деле говорит, что вам нужно запустить сценарий, который вы генерируете, он просто показывает, что для вас делает изменение через пользовательский интерфейс ...
это решение, которое я использовал - SSMS генерирует T-SQL для внесения изменений ... он делает это, создавая новую временную таблицу с тем же дизайном схемы, затем копируя в нее все строки, удаляя orig и переименовывая , может занять немного времени, чтобы запустить полностью, но это сработало отлично.
3

Primary key который имеетIdentity property
ТакRename или жеDelete столбец, который связан сPrimary Key constraint не будет возможно, потому что Правила ограничения проверяют структуру столбца.
Чтобы достичь этого, мы должны обработать некоторые шаги следующим образом:
ДопустимTableName = 'Employee' а такжеColumnName = 'EmployeeId'

1. Добавьте новый столбец «EmployeeId_new». в «Сотрудник»; Таблица
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

Now remove column 'EmployeeId' from 'Employee' table
ALTER TABLE Employee DROP COLUMN EmployeeId

This will throw error because of Primary Key Constraint rules are applicable and validating column structure.
*### 'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'###

So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column
ALTER TABLE Employee DROP constraint [PK_dbo.Employee]

Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error
ALTER TABLE Employee DROP COLUMN EmployeeId

Now Column 'EmployeeId' removed from table So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId'
sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId'
ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

8. Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint

Похожие вопросы