Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
Всем привет. Подскажите, была большая таблица с индексами, я создал копию таблицы и перенес в неё данные. Теперь я хочу удалить старую таблицу, но получаю вот такую ошибку:

Could not drop object 'dbo.Authors_old' because it is referenced by a FOREIGN KEY constraint. (Microsoft SQL Server, Error: 3726)


Подскажите, как в правильном порядке удалить этот ключ и удалить таблицу?
9 ноя 20, 12:56    [22228684]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
teCa,
1. Найти все таблицы, которые ссылаются на удаляемую, удалить там соответствующие FOREIGN KEY
2. Удалить таблицу
9 ноя 20, 13:12    [22228700]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
aleks222
Member

Откуда:
Сообщений: 1086
DeColo®es
teCa,
1. Найти все таблицы, которые ссылаются на удаляемую, удалить там соответствующие FOREIGN KEY
2. Удалить таблицу


Плохой совет.

Судя по всему, тредстартер не лыка не вяжет в том "что там у него за связи между таблицами".
Щас он фсе удалит, а потом прискачет с вопросом "а чего у меня ничего не работает"?
9 ноя 20, 13:38    [22228726]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
aleks222,

Именно, понятно, что сначала хотелось бы выяснить, что это за связь и как правильно с ней поступить.
9 ноя 20, 13:46    [22228732]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
aleks222
Плохой совет.

И, наверняка, ведь, кроме удаления старых FK надо будет создать такие же новые, на копию таблицы.
9 ноя 20, 14:32    [22228767]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
Надо сначала выполнить такой запрос:

Модератор: Не смешно


После этого спокойно уже удаляешь таблицу, как обычно.

Сообщение было отредактировано: 9 ноя 20, 14:46
9 ноя 20, 14:38    [22228774]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
fkthat
Надо сначала выполнить такой запрос:

После этого спокойно уже удаляешь таблицу, как обычно.


Смешно

Сообщение было отредактировано: 9 ноя 20, 14:46
9 ноя 20, 14:41    [22228777]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
teCa
aleks222,

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

https://ru.wikipedia.org/wiki/Внешний_ключ
9 ноя 20, 15:41    [22228825]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8047
teCa,

информацию о ключах можно получить из представления sys.foreign_key_columns.
9 ноя 20, 15:41    [22228826]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
Владислав Колосов,

Можно всё же побольше информации?

Используя select * from sys.foreign_key_columns, я получаю список из нескольких записей, вижу, что в списке указаны ИД, как я понимаю, сейчас мне нужно понять, какой ключ из данного списка нужен мне?
9 ноя 20, 15:54    [22228837]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
teCa,

https://google.gik-team.com/?q=sys.foreign_key_columns
9 ноя 20, 16:21    [22228850]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
teCa
Смешно

Обхохочешься

DECLARE @table_to_drop sysname = 'Customers'

DECLARE cur_fk CURSOR FOR
  SELECT [name] fk_name, object_name(parent_object_id) tbl_name
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id(@table_to_drop)

DECLARE @fk_name sysname,
  @tbl_name sysname,
  @sql nvarchar(512)

OPEN cur_fk
FETCH NEXT FROM cur_fk INTO @fk_name, @tbl_name
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @sql = 'alter table ' + @tbl_name + ' drop constraint ' + @fk_name
  EXEC sp_executesql @sql
  FETCH NEXT FROM cur_fk INTO @fk_name, @tbl_name
END
CLOSE cur_fk
DEALLOCATE cur_fk

Только это еще не создаст тебе ключи на копии таблицы (как я выше писал), а, скорее всего, это делать надо будет.
9 ноя 20, 16:31    [22228863]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
fkthat,

Да, изначально нужно перевести ключи на новую таблицу.
9 ноя 20, 17:02    [22228895]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
teCa
Да, изначально нужно перевести ключи на новую таблицу.
Самый простой способ для начинающих:

1. Разрешить в опциях SSMS пересоздавать таблицы из дизайнера.
2. Открыть в дизайнере старую таблицу и добавить какой-нибудь столбец. Переместить вновь добавленный столбец с последней позиции в любое другое место.
3. Получить скрипт изменений.
4. В данном скрипте удалить из тела транзакции все, кроме инструкцийй alter table ... drop constraint ... и alter table ... add constraint ...
5. Профит.
9 ноя 20, 17:26    [22228910]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
invm
teCa
Да, изначально нужно перевести ключи на новую таблицу.
Самый простой способ для начинающих:

1. Разрешить в опциях SSMS пересоздавать таблицы из дизайнера.
2. Открыть в дизайнере старую таблицу и добавить какой-нибудь столбец. Переместить вновь добавленный столбец с последней позиции в любое другое место.
3. Получить скрипт изменений.
4. В данном скрипте удалить из тела транзакции все, кроме инструкцийй alter table ... drop constraint ... и alter table ... add constraint ...
5. Профит.

Можно проще - скриптануть все нужные FK и в скрипте поменять имя reference-таблицы.
9 ноя 20, 18:07    [22228943]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
fkthat
Можно проще - скриптануть все нужные FK
Поделитесь знаниями - что нужно сказать штатному скриптеру, что бы такое провернуть?
9 ноя 20, 20:00    [22229049]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
invm
fkthat
Можно проще - скриптануть все нужные FK
Поделитесь знаниями - что нужно сказать штатному скриптеру, что бы такое провернуть?

Картинка с другого сайта.
9 ноя 20, 20:05    [22229061]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
fkthat,

Т.е. предлагаете скриптовать каждую дочернюю таблицу? И чем же это проще?
9 ноя 20, 20:30    [22229079]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31602
fkthat
invm
Поделитесь знаниями - что нужно сказать штатному скриптеру, что бы такое провернуть?

Картинка с другого сайта.
А если таблиц много, то можно скрипт на базе сделать, а потом скопировать ту часть, где FK.

Сообщение было отредактировано: 9 ноя 20, 20:29
9 ноя 20, 20:33    [22229085]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
я бы предложил воспользоваться Smo.
9 ноя 20, 21:01    [22229099]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
invm
Т.е. предлагаете скриптовать каждую дочернюю таблицу? И чем же это проще?

А, я понял о чем ты, просто изначально в твой хак не въехал. Да, если доч. таблиц много, то твоим способом быстрее.
9 ноя 20, 22:19    [22229131]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31602
fkthat
invm
Т.е. предлагаете скриптовать каждую дочернюю таблицу? И чем же это проще?

А, я понял о чем ты, просто изначально в твой хак не въехал. Да, если доч. таблиц много, то твоим способом быстрее.
Не понимаю смысла "хака", по моему, скриптануть все таблицы проще, меньше кликов.
10 ноя 20, 11:20    [22229393]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
alexeyvg
Не понимаю смысла "хака"
Смысл в том, что студия сама сгенерирует удаление/создание FK для дочерних таблиц. К тому же, еще и не обязательно знать перечень этих таблиц.
10 ноя 20, 11:30    [22229401]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
fkthat
Member

Откуда:
Сообщений: 3840
alexeyvg
fkthat
пропущено...

А, я понял о чем ты, просто изначально в твой хак не въехал. Да, если доч. таблиц много, то твоим способом быстрее.
Не понимаю смысла "хака", по моему, скриптануть все таблицы проще, меньше кликов.

Смысл в том, чтобы не скриптовать все дочерние таблицы/ключи по очереди, а скриптануть все это одним махом, вызвав изменения в таблице родительской.
10 ноя 20, 12:20    [22229439]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31602
А, речь о дочерних таблицах, да, это я упустил...
10 ноя 20, 18:16    [22229742]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
felix_ff
я бы предложил воспользоваться Smo.


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

$target = "myMasterTable"
$newtarget = "myMasterTableNew"
$instance = "(local)";
$server = New-Object("Microsoft.SqlServer.Management.Smo.server")
$db = $server.Databases["myDatabase"]
ForEach ($tbl in $db.Tables) {
  ForEach ($fk in $tbl.ForeignKeys) {
     if ($fk.ReferencedTable = $target) { echo $fk.Script().Replace($target, $newtarget) }
  }
}


в целом ничего не мешает добавить инструкцию для удаления FK.

Сообщение было отредактировано: 10 ноя 20, 18:48
10 ноя 20, 18:52    [22229757]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
invm
teCa
Да, изначально нужно перевести ключи на новую таблицу.
Самый простой способ для начинающих:

1. Разрешить в опциях SSMS пересоздавать таблицы из дизайнера.
2. Открыть в дизайнере старую таблицу и добавить какой-нибудь столбец. Переместить вновь добавленный столбец с последней позиции в любое другое место.
3. Получить скрипт изменений.
4. В данном скрипте удалить из тела транзакции все, кроме инструкцийй alter table ... drop constraint ... и alter table ... add constraint ...
5. Профит.


В данном примере, "получить скрипт изменений" возможно без перестроения таблицы? А то у меня в старой таблице все еще 1.8млрда записей.
11 ноя 20, 10:42    [22229983]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
teCa
В данном примере, "получить скрипт изменений" возможно без перестроения таблицы? А то у меня в старой таблице все еще 1.8млрда записей.
Я разве писал, что таблицу нужно реально перестраивать?
11 ноя 20, 10:46    [22229985]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
invm,
Просто тестирую на созданной таблице в студии, я добавляю столбец, меняю его место, нажимаю закрыть окно дизайнера. Сначала получаю диалог - "Сохранить внесенные изменения", нажимаю "Да", получаю диалог с скриптом и предложением его сохранить, после закрытия этого диалога, проверяю таблицу, столбец передвинулся.
11 ноя 20, 10:52    [22229987]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
По данной теме, я теперь знаю, что у той таблице, которую я хочу удалить, есть лишь одна связь, я знаю эту талицу и заскриптовал ключ, ссылающийся на старую таблицу:

ALTER TABLE [dbo].[BlogMetrics]  WITH CHECK ADD  CONSTRAINT [FK_BlogMetricsNew_Authors1] FOREIGN KEY([BlogId])
REFERENCES [dbo].[Blogs_old] ([Id])
GO

ALTER TABLE [dbo].[BlogMetrics] CHECK CONSTRAINT [FK_BlogMetricsNew_Authors1]
GO


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

ALTER TABLE [dbo].[BlogMetrics]  WITH CHECK ADD  CONSTRAINT [FK_BlogMetricsNew_Authors1] FOREIGN KEY([BlogId])
REFERENCES [b][dbo].[Blogs][/b] ([Id])
GO

ALTER TABLE [dbo].[BlogMetrics] CHECK CONSTRAINT [FK_BlogMetricsNew_Authors1]
GO


Я правильно всё понял?
11 ноя 20, 11:30    [22230014]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
teCa,

Не надо сохранять изменения. Нужно прямо в дизайнере получить скрипт изменений, нажав соответствующую кнопку на тулбаре.
11 ноя 20, 11:32    [22230016]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
invm
teCa,

Не надо сохранять изменения. Нужно прямо в дизайнере получить скрипт изменений, нажав соответствующую кнопку на тулбаре.


Спасибо, получился вот такой скрипт:

ALTER TABLE dbo.Blogs_old DROP CONSTRAINT DF_Blogs01_rep
GO
ALTER TABLE dbo.Blogs_old DROP CONSTRAINT DF_Blogs01_isEnabled
GO
ALTER TABLE dbo.Tmp_Blogs_old SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Blogs_old ADD CONSTRAINT
	DF_Blogs01_rep DEFAULT ((1)) FOR rep
GO
ALTER TABLE dbo.Tmp_Blogs_old ADD CONSTRAINT
	DF_Blogs01_isEnabled DEFAULT ((1)) FOR isEnabled
GO
ALTER TABLE dbo.BlogMetrics
	DROP CONSTRAINT FK_BlogMetricsNew_Authors1
GO
ALTER TABLE dbo.Blogs_old ADD CONSTRAINT
	PK_Blogs01 PRIMARY KEY CLUSTERED 
	(
	Id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON SSD_FG

GO
ALTER TABLE dbo.BlogMetrics ADD CONSTRAINT
	FK_BlogMetricsNew_Authors1 FOREIGN KEY
	(
	BlogId
	) REFERENCES dbo.Blogs_old
	(
	Id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.BlogMetrics SET (LOCK_ESCALATION = TABLE)
GO


Подскажите, команды для dbo.Tmp_Blogs_old, в моём контексте не нужны, так ведь?
11 ноя 20, 11:58    [22230031]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
teCa,

ALTER TABLE dbo.Blogs_old ADD CONSTRAINT
	PK_Blogs01 PRIMARY KEY CLUSTERED 
	(
	Id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON SSD_FG

GO


Это тоже вычеркиваем, как я понимаю? На новое таблице уже есть PK_Blogs01_bigint
11 ноя 20, 12:07    [22230036]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
teCa,

Перед удалением старой выполнить
ALTER TABLE dbo.BlogMetrics
	DROP CONSTRAINT FK_BlogMetricsNew_Authors1

Затем
ALTER TABLE dbo.BlogMetrics ADD CONSTRAINT
	FK_BlogMetricsNew_Authors1 FOREIGN KEY
	(
	BlogId
	) REFERENCES НоваяТаблица
	(
	Id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION
11 ноя 20, 12:31    [22230051]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 611
teCa
Всем привет. Подскажите, была большая таблица с индексами, я создал копию таблицы и перенес в неё данные. Теперь я хочу удалить старую таблицу, но получаю вот такую ошибку:

Could not drop object 'dbo.Authors_old' because it is referenced by a FOREIGN KEY constraint. (Microsoft SQL Server, Error: 3726)


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


А такая вот задача не решается ли простым переименованием старой таблицы?
Если новая это идентичная копия по структуре и данным, то в чем смысл всех этих манипуляций?

если делать через sp_rename, то и определения внешних ключей сами пофиксятся.

Сработает, если в той же схеме.

Сообщение было отредактировано: 11 ноя 20, 12:39
11 ноя 20, 12:42    [22230057]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
invm
teCa,

Перед удалением старой выполнить
ALTER TABLE dbo.BlogMetrics
	DROP CONSTRAINT FK_BlogMetricsNew_Authors1

Затем
ALTER TABLE dbo.BlogMetrics ADD CONSTRAINT
	FK_BlogMetricsNew_Authors1 FOREIGN KEY
	(
	BlogId
	) REFERENCES НоваяТаблица
	(
	Id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION


Блокировок таблиц не предусматривает данные действия?
11 ноя 20, 12:56    [22230072]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
teCa,

При создании нового ключа, получаю такую ошибку:

автор
Столбец «dbo.Blogs.Id» не является тем же типом данных, что и столбец «BlogMetrics.BlogId» во внешнем ключе «FK_BlogMetricsNew_Authors1».


В данной таблице я вижу всего одну строку. Могу ли я через дизайнер просто сменить тип на bigint после удаления внешнего влюча?
11 ноя 20, 13:40    [22230106]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблицы с первичным ключом?  [new]
teCa
Member

Откуда:
Сообщений: 684
teCa,

Пересоздал ключ.
11 ноя 20, 13:44    [22230110]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить