Как удалить связь между таблицами sql
Надеюсь из прошлых, почти полностью теоретических, изложений стало немного яснее, что же все-таки есть реляционные данные и все, что с ними связано. Давайте сейчас попробуем просмотреть все это практически. Наша с вами учебная БД в схеме miller содержит, пять таблиц. Все они в принципе отвечают требованиям 3НФ. Но, когда я их создавал, я не связал столбцы этих таблиц между собой с помощью стандартных средств. А, вот сейчас давайте мы с вами это сделаем. Итак для примера организуем связь, которая чаше всего рекомендована к применению, типа один-ко-многим. Ярким примером для построения такой связи служит две из наших пяти таблиц это CUSTOMERS и SALESREPS. Оператор CREATE TABLE их DDL определений записан следующим образом:
Таблица CUSTOMERS:
Таблица SALESREPS:
При просмотре данных, этих таблиц почти сразу видно, что столбец таблицы SALESREPS — EMPL_NUM есть отношение один-ко-многим столбца CUST_REP для таблицы CUSTOMERS. Для определения связи между таблицами воспользуемся оператором ALTER TABLE и запишем вот такую конструкцию:
Все, связь между столбцами таблиц установлена! Все достаточно просто. Теперь действует ограничение ссылочной целостности и нарушить его нам с вами не позволят! Можно убедиться в этом. Столбец таблицы SALESREPS — EMPL_NUM содержит следующее множество значений 101 .. 110 и отдельно 120. Попробуйте что-нибудь вроде:
После ввода, получаем:
Естественно ошибка ORA-02291! А все потому, что множество 101 .. 110 и отдельно 120 не содержит числа 150! И по этому в данном случае не допустимо! Вот и получилось жесткое отношение один-ко-многим! Так же, можно и удалить связь, между столбцами таблиц применив оператор DROP. Но, нужно узнать имя ссылочной целостности в системе. Сейчас мы его знаем благодаря ошибке. А что если, в процессе работы нужно удалить ссылочную целостность, а потом снова восстановить ее! Для этого обратимся к представлению в вашей схеме USER_CONSTRAINTS. Оно содержит все имена ваших ограничений. Дадим такой запрос:
Там где поле CONSTRAINT_TYPE содержит значение R и есть наше ограничение (по моему от REFERENCES, точно не помню!) Получаем имя ограничения — SYS_C003548 (тоже номер был и в ошибке помните?). Вот теперь давайте от него избавимся:
Вот теперь ограничение снято. Повторим наш предыдущий запрос и посмотрим, что содержит USER_CONSTRAINTS сейчас:
Хорошо видно, что осталось только ограничение первичного ключа таблицы CUSTOMERS имеющее имя SYS_C003506. Кроме того, таблица может содержать ограничение на саму себя например все с той же таблицей SALESREPS можно проделать следующее:
Теперь таблица, как бы это лучше сказать — «самоограничилась», хотя это не всегда оправдано, но вполне применимо и может использоваться! Можете сами с этим всем поработать и определить приоритеты, при проектировании БД, оптимизации и определении ссылочных целостностей таблиц! Но, слишком не увлекайтесь, границы сознания не бесконечны и не стоит выходить за границы понимания, а уж во всяком случае выпускать за них свою БД. 🙂
Удаление связанных записей в MySQL одним запросом
В MySQL есть возможность удалять связанные данные через внешние ключи (foreign key). Но это возможно только для некоторых движков БД (например, InnoDB) и в том случае, если для ключей задано ограничение ON DELETE CASCADE . В противном случае придется удалять записи вручную. Как раз об этом и пойдет речь в этой заметке.
Допустим у нас есть 2 таблицы: таблица товаров и изображений товаров:
- product (id, name, price, description, active)
- product_image (id, product_id, src, alt)
И нам необходимо удалить все неактивные товары вместе с изображениями. Для этого придется написать DELETE запрос с JOIN -ом и указать таблицы в которых необходимо произвести удаление данных.
Запрос, который решит указанную выше задачу, будет выглядеть следующим образом:
Если архитектура базы подразумевает связь еще через таблицу, то добавляем еще один джоин. Запрос удаляет только таблицы указанные после DELETE , остальные таблицы, которые участвуют в запросе, останутся без изменений.
How to remove foreign key constraint in sql server?
I want to remove foreign key from another table so i can insert values of my choice.
I am new in databases so please tell me correct sql query to drop or remove foreign key value.
15 Answers 15
Its wrong to do that in refer to referential integrity, because once its broken its not easy to turn it on again without having to go through the records and delete the ones which breaks the constraints.
Anyway the Syntax is as follows:
But, be careful man, once you do that, you may never get a chance back, and you should read some basic database book see why we need foreign key
To remove all the constraints from the DB:
You should consider (temporarily) disabling the constraint before you completely delete it.
If you look at the table creation TSQL you will see something like:
. then insert/update a bunch of values that violate the constraint, and then turn it back on by running the original CHECK statement.
(I have had to do this to cleanup poorly designed systems I’ve inherited in the past.)
Drop all the foreign keys of a table:
Depending on the DB you are using there’s a syntax or another.
If you’re using Oracle you have to put what the other users told you:
But if you use MySQL then this will give you a syntax error, instead you can type:
to see the descriptive structure of your table.
There you may see constraints respective to foreign keys you used in that table. First delete the respective constraint with
and then delete the respective foreign keys or column you wanted. GoodLuck!!
EDIT: didn’t notice you were using sql-server, my bad
Use those queries to find all FKs:
Alternatively, you can also delete a Foreign Key Constraint from the SQL Server Management Studio itself. You can try it if the commands do not work.
- Expand your database view.
- Right Click on Table which has foreign key constraint. Choose Design. A tab with the information about table columns will open.
- Right click on the column which has the foreign key reference. Or you can right click on any column. Choose Relationships.
- A list of relationships will appear (if you have one) in a pop up window.
- From there you can delete the foreign key constraint.
I hope that helps
If you find yourself in a situation where the FK name of a table has been auto-generated and you aren’t able to view what it exactly is (in the case of not having rights to a database for instance) you could try something like this:
Build up a stored proc which drops the constraint of the specified table without specifying the actual FK name. It drops the constraint where the object [type] is equal to F (Foreign Key constraint).
Note: if there are multiple FK’s in the table it will drop them all. So this solution works best if the table you are targeting has just one FK.
jtest.ru
Сегодня мы продолжаем наше путешествие в мире SQL и реляционных систем управления базами данных. В этой части мы научимся работать с несколькими таблицами связанными между собой. Сначала мы познакомимся с базовыми концепциями, а потом начнем работать с запросами JOIN в SQL.
Предыдущие статьи
Вступление
При проектировании базы данных, здравый смысл подсказывает нам, что мы должны использовать различные таблицы для разных данных. Пример: клиенты, заказы, записи, сообщения и т.д. Так же мы должны иметь взаимосвязи между этими таблицами. Например, клиент имеет заказы, а у заказа есть позиции (товары). Эти взаимосвязи должны быть отражены в базе данных. А также, когда мы получаем данные с помощью SQL, мы должны использовать определенные типы запросов JOIN, чтобы получить нужный результат.
Вот несколько типов отношений в базе данных. Сегодня мы рассмотрим следующие:
- Отношения один к одному
- Один ко многим и многие к одному
- Многие ко многим
- Связь с самим собой
Когда данные выбираются из нескольких связанных таблиц, мы будем использовать запрос JOIN. Есть несколько типов присоединения, мы познакомимся с этими:
- Cross Joins (Перекрестное соединение)
- Natural Joins (Естественное соединений)
- Inner Joins (Внутреннее соединений)
- Left (Outer) Joins (Левое (внешнее) соединение)
- Right (Outer) Joins (Правое (внешнее) соединение)
Также мы изучим предложения ON и USING.
Связь один к одному
Допустим есть таблица покупателей (customers):
Мы можем расположить информацию о адресе покупателя в другой таблице:
Теперь у нас есть связь между таблицами покупателей (Customers) и адресами (Addresses). Если каждый адрес может принадлежать только одному покупателю, то такая связь называется «Один к одному». Имейте ввиду, что такой тип отношений не очень распространен. Наша первоначальная таблица, в которой информация о покупателе и его адресе хранилась вместе, в большинстве случаев работает нормально.
Обратите внимание, что теперь поле с названием «address_id», в таблице покупателей, ссылается на соответствующую запись в таблице адресов. Оно называется внешним ключом (Foreign Key) и используется во всех видах связей в базе. Мы рассмотрим этот вопрос позже в этой статье.
Вот так можно отобразить отношения между покупателями и адресами:
Обратите внимание, что существование данных отношений не обязательно, например, может существовать запись о покупателе без связанной записи о его адресе.
Связь один ко многим и многие к одному
Этот тип отношений наиболее часто встречающийся. Рассмотрим такой сайт интернет магазина:
- У покупателей может быть несколько заказов.
- Заказ может содержать несколько товаров.
- Товары могут иметь описание на нескольких языках.
В этих случаях нам потребуется создать связь «Один ко многим». Пример:
Каждый покупатель может иметь 0 или более заказов. Но каждый заказ может принадлежать только одному покупателю.
Связь многие ко многим
В некоторых случаях требуется многочисленные связи по обе стороны отношений. Например, каждый заказ может содержать множество товаров. И каждый товар может присутствовать во многих заказах.
Для такой связи нам потребуется создать дополнительную таблицу:
Назначение таблицы «Items_Orders» только одно — создать связь «Многие ко многим» между товарами и заказами.
Так можно представить этот тип отношений:
Если добавить записи items_orders к диаграмме, то она будет выглядеть так:
Связь с собой
Такой тип используется когда у таблицы должны быть связь с собой. Допустим у Вас есть реферальная программа. Покупатели могут ссылаться на других покупателей на вашем сайте интернет магазина. Таблица может выглядеть так:
Покупатели 102 и 103 ссылаются на покупателя 101.
Этот тип похож на связь «Один ко многим», поскольку один покупатель может ссылаться на несколько покупателей. Это можно представить как древовидную структуру:
Один покупатель может ссылаться на одного покупателя, на нескольких покупателей, или вообще не ссылаться ни на одного.
Если Вы хотите создать связь внутри таблицы «многие ко многим», то потребуется создать дополнительную таблицу, такую же как и в предыдущей части.
Внешние ключи
Пока что мы говорили только о базовых вещах. Пришло время применить полученные знания на практике, используя SQL. В данной часть нам нужно понять, что из себя представляют внешние ключи.
В отношениях, обсуждаемых выше, у нас всегда было поле вида «****_id», которое ссылалось столбец в другой таблице. В нашем примере столбец customer_id, в таблице Orders, является внешним ключом:
В таких базах как MySQL есть два способа создания внешних ключей:
Задать внешний ключ явно
Создадим простую таблицу с покупателями:
Теперь создадим таблицу заказов, которая будет содержать вторичный ключ:
Оба столбца (customers.customer_id и orders.customer_id) должны быть одного типа. Если у первого тип INT, то второй не должен быть типа BIGINT, например.
Пожалуйста, помните, что в MySQL полностью поддерживает внешние ключи только подсистема InnoDB. Другие подсистемы хранения данных позволяют определять внешние ключи без каких либо ошибок. Столбцы с внешними ключами индексируются автоматически, если явно не задать другой индекс.
Без явного объявления
Некоторые таблицы заказов могут быть созданы без явного определения внешнего ключа:
Когда данные получают запросом JOIN, Вы можете использовать столбец как внешний ключ, хотя база данных не знает о этих связях.
Мы подошли к изучению запросов JOIN, которые обсудим далее в статье.
Отображение связей
В данный момент, моей любимой программой для проектирования баз данных и отображения связей является MySQL Workbench.
После того как Вы спроектировали базу данных, ее можно экспортировать в SQL и выполнить на сервере. Это очень удобно при создании больших и сложных баз данных.
Запросы JOIN
Чтобы получить связанные данные из базы данных следует использовать запросы JOIN.
Прежде чем мы начнем, давайте создадим для работы тестовые таблицы и данные.
У нас есть 4 покупателя. У одного из них два заказа, у двоих по одному заказу, и у одного вообще нет заказов. Теперь давайте посмотрим какие виды запросов JOIN мы можем выполнять с этими таблицами.
Cross Join (Перекрестное объединение)
Это вид JOIN запроса по-умолчанию, если не определено условие.
Результатом будет, так называемое, «Декартово объединение» таблиц. Это означает, что каждая строка из первой таблицы сопоставляется с каждой строкой второй таблицы. Т.к. в каждой таблице по 4 строки, мы получили в результате 16 строк.
Ключевое слово JOIN можно заменить на запятую, в этом случае.
Конечно такой результат почти бесполезен. Давайте взглянем на другие виды объединений.
Natural Join (Естественное объединение)
При таком виде запроса JOIN таблицы должны иметь совпадающие, по имени, столбцы. В нашем случае в обеих таблицах должен присутствовать столбец customer_id. MySQL объединит записи только в случае совпадения значений в этих столбцах.
Как Вы можете видеть, в этот раз столбец customer_id отображаются только один раз, потому что движок базы рассматривает этот столбец как общий. Мы видим два заказа Adam’а, и другие два заказа Joe и Sandy. Наконец мы получили некоторую полезную информацию.
Inner Join (Внутреннее объединение)
Если условие объединения не указан, то выполняется внутреннее объединение. В этом случае хорошей идеей будет наличие совпадений по полю customer_id в обеих таблицах. Результат должен быть аналогичен естественному объединению.
Результат почти такой же. Столбец customer_id повторяется два раза, по разу для каждой таблицы. Объясняется это тем, что мы попросили базу сравнить значение по двум столбцам. При этом не знаю, что возвращают одну и туже информацию.
Добавим побольше условий к запросу.
На этот раз возвращается только те заказы, сумма которых превышает $15.
Предложение ON
Прежде чем перейти к другим видам объединяющих запросов, нам нужно рассмотреть предложение ON. Оно служит для вставки условий JOIN в отдельные предложения.
Теперь мы можем различать условия, относящиеся к JOIN и условия в части WHERE. Но еще есть небольшая разница в функционировании. Мы увидим это, когда перейдем к примерам с LEFT JOIN.
Предложение USING
Предложение USING немного похоже на конструкцию ON. Если столбцы в таблицах называется одинаково, можно указать их здесь.
На самом деле это очень похоже на NATURAL JOIN, т.е. объединяющий столбец (customer_id) не повторяется дважды.
Left (Outer) Join (Левое внешнее соединение)
LEFT JOIN это вид внешнего соединения. В следующем запросе, если не найдены совпадения во второй таблице, записи из первой таблице все равно отобразятся.
Хотя у Andy и нет заказов, эта запись все равно отображается. Значение из второй таблицы равно NULL.
Это полезно, когда нужно найти записи, у которых нет связей. Например, мы можем найти всех покупателей, которые ничего не заказывали.
Все что мы сделали — нашли все значения NULL для order_id.
Отметим, что ключевое слово OUTER не обязательно. Вы можете использовать просто LEFT JOIN вместо LEFT OUTER JOIN.
Условия
Теперь давайте посмотрим на запросы с условиями.
Так, что случилось с Andy и Sandy? LEFT JOIN подразумевает, что мы должны получить покупателей, у которых нет заказов. Проблема в том, что условие WHERE скрывает эти результаты. Чтобы получить их, мы можем попытаться включить условие с NULL.
Появился Andy, но нет Sandy. Выглядит неправильно. Для того чтобы получить то, что мы хотим, нужно использовать предложение ON.
Теперь мы получили всех, и все заказы более $15. Как я говорил ранее, предложение ON иногда работает не так как WHERE. В таких внешних объединениях как это, столбцы включаются всегда, даже если нет совпадений в условии предложения ON.
Right (Outer) Join (Правое внешнее соединение)
Объединение RIGHT OUTER JOIN работает также, только порядок таблиц меняется на обратный.
На этот раз мы не получили результатов с NULL, потому что каждый заказ имеет сопоставление с записью покупателя. Мы можем поменять порядок таблиц и получим тот же результат, что и с LEFT OUTER JOIN.
Теперь у нас появились значения NULL, потому что таблица покупателей с правой стороны от объединения.