Как удалить связь между таблицами sql
Чтобы удалить связь между таблицами в SQL, нужно использовать оператор ALTER TABLE и удалить внешний ключ, который связывает две таблицы.
Вот пример SQL запроса для удаления внешнего ключа «fk_column» из таблицы «my_table» :
ALTER TABLE my_table DROP CONSTRAINT fk_column; В этом примере оператор ALTER TABLE используется для изменения таблицы «my_table» , а ключевое слово DROP CONSTRAINT указывает на то, что необходимо удалить внешний ключ из таблицы.
Обратите внимание, что при удалении внешнего ключа могут потребоваться изменения в других таблицах, связанных с этим ключом. В зависимости от структуры базы данных, может потребоваться удалить или изменить соответствующие записи в связанных таблицах, чтобы сохранить целостность данных.
Удаление связи между таблицами
НЕ умею удалять связь между таблицей A и B через sql . Умею только через phpadmin графически. Там все понятно кликаешь на связь.Выскакивает табличка , и спокойно она удаляется. А как это сделать через консоль в mysql? Связывать не графически умею.
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Связи между 7 таблицами
Приветствую, нужно грамотно сделать связи в бд между 7 таблицами. del].
Связи между таблицами
помогите пожалуйста! не могу никак связать таблицы в mysql workbench, все время да будет какая.
Связи между таблицами
Здравствуйте, помогите со связями между таблицами работаю в mysql 5.5 command line client вот у.
Ошибка связи между таблицами
При создании связи между таблицами users & history по ключу key_users выдается ошибка "Can’t.
Как удалить связь между таблицами sql
Скачай курс
в приложении
Перейти в приложение
Открыть мобильную версию сайта
© 2013 — 2023. Stepik
Наши условия использования и конфиденциальности
Public user contributions licensed under cc-wiki license with attribution required
Удаление значений из связанных таблиц
Значит, есть задача: существует несколько (неизвестное количество) таблиц, они связаны между собой средствами FOREIGN — PRIMARY KEY . Нужно удалить все записи с таблиц. К таблицам подключаться я не могу. Могу только подключиться к 1 (любой) и из неё вести уже удаление всех зависящих таблиц. Код:
USE DB; CREATE PROC DB_CLEAR @TABLE_NAME NVARCHAR(400) AS BEGIN TRY EXEC('DELETE FROM ' + @TABLE_NAME) END TRY BEGIN CATCH DECLARE @ERR_FULL_MESSAGE NVARCHAR(400), @TEMP_STRING NVARCHAR(100), @START_TABLE_INDEX SMALLINT, @END_TABLE_INDEX SMALLINT,-- @DEPENDED_TABLE NVARCHAR(100), ---------------------------------- @DEPENDED_COLUMN NVARCHAR(100), @DEPENDED_COLUMN_TEMP NVARCHAR(100), @OWN_ID_START_INDX SMALLINT, @OWN_ID_END_INDX SMALLINT --------------------------------------------------------------------------------------------- SELECT @ERR_FULL_MESSAGE = ERROR_MESSAGE(), @START_TABLE_INDEX = CHARINDEX('table',ERROR_MESSAGE()) SET @START_TABLE_INDEX = @START_TABLE_INDEX + 7 SELECT @TEMP_STRING = SUBSTRING(ERROR_MESSAGE(),@START_TABLE_INDEX,LEN(ERROR_MESSAGE())) SET @END_TABLE_INDEX = CHARINDEX('column',@TEMP_STRING) SET @END_TABLE_INDEX = @END_TABLE_INDEX - 3; SET @DEPENDED_TABLE = SUBSTRING(@TEMP_STRING,0,@END_TABLE_INDEX) ---------------------------------------------------------------------------------------------- SELECT @OWN_ID_START_INDX = CHARINDEX('column', ERROR_MESSAGE()) SET @OWN_ID_START_INDX = @OWN_ID_START_INDX + 8 SELECT @DEPENDED_COLUMN_TEMP = SUBSTRING(ERROR_MESSAGE(),@OWN_ID_START_INDX,LEN(ERROR_MESSAGE())) SET @OWN_ID_END_INDX = CHARINDEX('.',@DEPENDED_COLUMN_TEMP) SET @OWN_ID_END_INDX = @OWN_ID_END_INDX - 1; SET @DEPENDED_COLUMN = SUBSTRING(@DEPENDED_COLUMN_TEMP,0,@OWN_ID_END_INDX) ----------------------------------------------------------------------------------------------- BEGIN TRY EXEC ('UPDATE ' + @DEPENDED_TABLE + ' SET ' + @DEPENDED_COLUMN + ' = NULL ' + ' WHERE Name != ''FIELD_NAME'' ') --THE FIELD NEEDED TO BE! EXEC ('UPDATE ' + @TABLE_NAME + ' SET ' + @DEPENDED_COLUMN + ' = NULL' + 'WHERE ID != ''668241BB-86E3-49B6-98BA-E746F06730F9''') --THE FIELD NEEDED TO BE! END TRY BEGIN CATCH PRINT ERROR_MESSAGE() COLUMN_REMOVER @DEPENDED_TABLE, @DEPENDED_COLUMN END CATCH END CATCH GO EXEC DB_CLEAR 'dbo.TABLE_NAME' -- ТАБЛИЦА КОТОРУЮ НУЖНО УДАЛИТЬ(ПАРАМЕТР) GO ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE PROC COLUMN_REMOVER @TABLE NVARCHAR(100) @COLUMN NVARCHAR(100) AS BEGIN TRY EXEC ('UPDATE ' + @TABLE + ' SET ' + @COLUMN + ' = NULL') END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH GO
Отслеживать
33.9k 25 25 золотых знаков 130 130 серебряных знаков 222 222 бронзовых знака
задан 17 авг 2015 в 13:30
Boris Danylenko Boris Danylenko
41 5 5 бронзовых знаков
на русском, плиз
17 авг 2015 в 13:33
Я голосую за закрытие этого вопроса как не соответствующего теме, потому что он не задан на русском языке
17 авг 2015 в 13:34
Воспользуйтесь этой инструкцией, чтобы добавить в вопрос всю информацию, необходимую для решения: meta.ru.stackoverflow.com/q/169/181472
17 авг 2015 в 14:31
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Так как таблицы и связи между ними заранее не известны то, чтобы удалить все связанные данные придется создавать динамические запросы. Для этого можно воспользоваться описанием связей foreign key — primary key получив его из information_schema.
SqlFiddle что-то ругается на пример в отличии от SSMS так что выкладываю пример здесь.
Для начала создадим несколько таблиц и наполним их данными:
create table TableOne ( id int primary key ); create table TableTwo ( id int primary key, tableOne_id int foreign key references TableOne(id) ); create table TableThree ( id int primary key, tableTwo_id int foreign key references TableTwo(id) ); create table TableFour ( id int, tableThree_id int foreign key references TableThree(id) ); create table TableFive ( id int, tableOne_id int foreign key references TableOne(id) ) insert into TableOne (id) values(1); insert into TableOne (id) values(2); insert into TableTwo (id, tableOne_id) values(1, 1); insert into TableTwo (id, tableOne_id) values(2, 1); insert into TableThree (id, tableTwo_id) values(3, 1); insert into TableThree (id, tableTwo_id) values(4, 1); insert into TableFour (id, tableThree_id) values(5, 3); insert into TableFour (id, tableThree_id) values(6, 3); insert into TableFive (id, tableOne_id) values(1, 2); insert into TableFive (id, tableOne_id) values(2, 2); Теперь создадим процедуру, которая внутри себя будет использовать рекурсивный запрос к метаданным для получения динамического запроса на удаления связанных строк:
CREATE PROCEDURE clear_table @table_name VARCHAR(max) AS BEGIN DECLARE @DROP_QUERY varchar(max); DECLARE QUERY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR with fk (query, deleteQuery, master_table, detail_table, master_column_name, detail_column_name, ord) as ( select cast('select * from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from ' + mccu.TABLE_NAME + ')'as varchar(max)) as query, cast('delete from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from ' + mccu.TABLE_NAME + ')' as varchar(max)) deleteQuery, mccu.TABLE_NAME master_table, ctu.TABLE_NAME detail_table, mccu.COLUMN_NAME master_column_name, dccu.COLUMN_NAME detail_column_name, 1 ord from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE mccu join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_NAME = mccu.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE dccu on dccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on ctu.constraint_name = rc.CONSTRAINT_NAME where mccu.table_name = @table_name union all select cast('select * from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from (' + fk.query + ') s )'as varchar(max)) as query, cast('delete from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from (' + fk.query + ') s )' as varchar(max)) deleteQuery, mccu.TABLE_NAME master_table, ctu.table_name detail_table, mccu.COLUMN_NAME master_column_name, dccu.COLUMN_NAME detail_column_name, fk.ord + 1 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE mccu join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_NAME = mccu.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE dccu on dccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on ctu.constraint_name = rc.CONSTRAINT_NAME join fk on fk.detail_table = mccu.table_name ) select deleteQuery from ( select deleteQuery, ord from fk union all select 'delete from ' + @table_name deleteQuery, 0 ord ) s order by ord desc OPEN QUERY_CURSOR FETCH NEXT FROM QUERY_CURSOR INTO @DROP_QUERY WHILE @@FETCH_STATUS = 0 BEGIN EXEC(@DROP_QUERY) FETCH NEXT FROM QUERY_CURSOR INTO @DROP_QUERY END CLOSE QUERY_CURSOR DEALLOCATE QUERY_CURSOR END; Теперь попробуем с помощью нее удалить все данные из таблицы TableOne . Как видно из кода выше на таблицу TableOne ссылаются таблицы TableTwo и TableFive , а на таблицу TableTwo ссылается таблица TableThree и т.п. Но, благодаря использованию процедуры все связанные данные будут удалены:
exec clear_table 'TableOne' Чтобы посмотреть какие запросы на удаление генерируются в процедуре можно отдельно выполнить рекурсивный запрос и посмотреть на результат его работы. В нашем случае для таблицы TableOne будут сгенерированны и последовательно выполнены следующие запросы:
delete from TableFour where tableThree_id in (select id from (select * from TableThree where tableTwo_id in (select id from (select * from TableTwo where tableOne_id in (select id from TableOne)) s )) s ) delete from TableThree where tableTwo_id in (select id from (select * from TableTwo where tableOne_id in (select id from TableOne)) s ) delete from TableTwo where tableOne_id in (select id from TableOne) delete from TableFive where tableOne_id in (select id from TableOne) delete from TableOne UPDATE
Чтобы удалить данные только из той таблицы, имя которой нам передано в параметрах, нужно сначала удалить все foreign key которые смотрят на ее primary key , а затем уже стереть из нее данные. Для этого можно воспользоваться той же метаинформацией и сгенерировать запросы на удаление foreign key после чего уже удалить данные из самой таблицы:
CREATE PROCEDURE clear_table2 @table_name VARCHAR(max) AS BEGIN DECLARE @DROP_QUERY varchar(max); DECLARE QUERY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR select 'alter table ' + ctu.table_name + ' drop constraint ' + rc.CONSTRAINT_NAME query from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE mccu join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_NAME = mccu.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on ctu.constraint_name = rc.CONSTRAINT_NAME where mccu.table_name = @table_name union all select 'delete from ' + @table_name query OPEN QUERY_CURSOR FETCH NEXT FROM QUERY_CURSOR INTO @DROP_QUERY WHILE @@FETCH_STATUS = 0 BEGIN EXEC(@DROP_QUERY) FETCH NEXT FROM QUERY_CURSOR INTO @DROP_QUERY END CLOSE QUERY_CURSOR DEALLOCATE QUERY_CURSOR END; Если вызвать ее с параметром TableOne то она сгенерирует и выполнит следующие запросы (имя констрейнтов может отличаться):
alter table TableFive drop constraint FK__TableFive__table__004002F9 alter table TableTwo drop constraint FK__TableTwo__tableO__7993056A delete from TableOne UPDATE 2
Чтобы уже сильно не менять процедуру доработаем ее так, как есть, добавив возможность указывать фильтр влияющий на удаляемые данные. Для этого добавим к процедуре еще один параметр и немного модифицируем способ генерации запросов:
CREATE PROCEDURE clear_table3 @table_name VARCHAR(max), @predicate varchar(max) AS BEGIN DECLARE @DROP_QUERY varchar(max); DECLARE QUERY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR with fk (query, deleteQuery, master_table, detail_table, master_column_name, detail_column_name, ord) as ( select cast('select * from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from ' + mccu.TABLE_NAME + ' ' + @predicate + ')'as varchar(max)) as query, cast('delete from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from ' + mccu.TABLE_NAME + ' ' + @predicate + ')' as varchar(max)) deleteQuery, mccu.TABLE_NAME master_table, ctu.TABLE_NAME detail_table, mccu.COLUMN_NAME master_column_name, dccu.COLUMN_NAME detail_column_name, 1 ord from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE mccu join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_NAME = mccu.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE dccu on dccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on ctu.constraint_name = rc.CONSTRAINT_NAME where mccu.table_name = @table_name union all select cast('select * from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from (' + fk.query + ') s )'as varchar(max)) as query, cast('delete from ' + ctu.TABLE_NAME + ' where ' + dccu.COLUMN_NAME + ' in (select ' + mccu.COLUMN_NAME + ' from (' + fk.query + ') s )' as varchar(max)) deleteQuery, mccu.TABLE_NAME master_table, ctu.table_name detail_table, mccu.COLUMN_NAME master_column_name, dccu.COLUMN_NAME detail_column_name, fk.ord + 1 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE mccu join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.UNIQUE_CONSTRAINT_NAME = mccu.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE dccu on dccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on ctu.constraint_name = rc.CONSTRAINT_NAME join fk on fk.detail_table = mccu.table_name ) select deleteQuery from ( select deleteQuery, ord from fk union all select 'delete from ' + @table_name + ' ' + @predicate deleteQuery, 0 ord ) s order by ord desc OPEN QUERY_CURSOR FETCH NEXT FROM QUERY_CURSOR INTO @DROP_QUERY WHILE @@FETCH_STATUS = 0 BEGIN EXEC(@DROP_QUERY) FETCH NEXT FROM QUERY_CURSOR INTO @DROP_QUERY END CLOSE QUERY_CURSOR DEALLOCATE QUERY_CURSOR END; Если вызвать эту процедур, например, так:
exec clear_table3 'TableOne', 'where id != 1' То сформируются такие запросы:
delete from TableFour where tableThree_id in (select id from (select * from TableThree where tableTwo_id in (select id from (select * from TableTwo where tableOne_id in (select id from TableOne where id != 1)) s )) s ) delete from TableThree where tableTwo_id in (select id from (select * from TableTwo where tableOne_id in (select id from TableOne where id != 1)) s ) delete from TableTwo where tableOne_id in (select id from TableOne where id != 1) delete from TableFive where tableOne_id in (select id from TableOne where id != 1) delete from TableOne where id != 1 Которые удалят только то, что нам нужно.