Cascade constraints oracle что это
Речь идет о внешних ключах. Вопросы слушателей заставили недавно освежить мои представления о них. В итоге я
(а) приношу извинения сразу всей своей прошлой аудитории за чересчур категоричные утверждения о поведении внешних ключей в Oracle
(б) привожу в этой статье некоторые свойства внешних ключей, расширяющие их общеизвестные возможности.
Все же, приводимые ниже возможности имеют специальный, а не универсальный характер. Кроме того каскадное удаление я по-прежнему считаю довольно рискованной в общем случае операцией.
Определение внешнего ключа
Внешний ключ в Oracle вслед за стандартным SQL реализован как разновидность ограничения. Информация о нем включается в Oracle в системную таблицу USER_CONSTRAINTS.
Внешний ключ в демонстрационной схеме SCOTT в Oracle существует: это поле EMP.DEPTNO, ссылающееся на поле первичного ключа DEPT.DEPTNO. Совпадения имен полей не требуется, но когда оно возможно, это удобно, так как подчеркивает содержательную связь.
Завести внешний ключ можно сразу при создании таблицы, или же потом, командой
Внешний ключ может ссылаться на поля таблицы из другой схемы
Для того, чтобы иметь возможность сослаться внешним ключом на поле таблицы в другой схеме, на это поле должна иметься привилегия REFERENCING. Особо нужно отметить, что с привилегиями SELECT, INSERT, UPDATE и DELETE привилегия REFERENCING никак не связана. Иными словами схема с подчиненной таблицей может ничего не знать о конкретных значениях ключа, на которые есть возможность ссылаться, равно как на наличие других полей в таблице. Пример:
Последняя вставка будет вызывать ошибку до тех пор, пока в таблице SCOTT.DEPT не появится запись об отделе 50.
(Привилегия SELECT на таблицу EMP была выдана пользователю ADAM исключительно для возможности скопировать эту таблицу).
Обозначенная выше возможность довольно своеобразна, так как разрушает самодостаточность схемы с точки зрения формирования данных. Если она использована, то ни схема с родительской таблицей, ни схема с подчиненной таблицей уже не могут безоглядно править собственные данные и вынуждены координировать свои действия с данными из других схем.
Тем не менее практика баз данных настолько разнообразна, что указанная возможность иногда может оказаться востребованной.
Удаление родительской записи может автоматически изменять подчиненные таблицы
DELETE FROM dept WHERE deptno = 10;
Однако Oracle позволяет смоделировать и иную реакцию СУБД, все-таки разрешив удаление родительской записи. Для этого при создании внешнего ключа нужно специально указать фразу ON DELETE.
Указание ON DELETE CASCADE приведет к автоматическому удалению подчиненных записей: При этом автоматическое удаление может распространяться по цепочке: (Автоматическим удалением по цепочке следует пользоваться с особой осторожностью).
Указание ON DELETE SET NULL приведет к автоматическому удалению значений в полях-ссылках подчиненных записей: Обратите внимание, что фраза CASCADE CONSTRAINTS в предложении DROP TABLE не соответствует ни первому, ни второму из вышеприведенных вариантов, попросту удаляя ограничение типа «внешний ключ», и не трогая значений подчиненных записей: Попутно обратите внимание, что если бы в предложениях DROP выше не фигурировала фраза CASCADE CONSTRAINTS, удалять таблицы пришлось бы в строго определенном порядке. Но это же обеспечивает в общем более «чистые» данные в БД, так что как правило фразы CASCADE CONSTRAINTS следует избегать.
Oracle DROP TABLE
Summary: in this tutorial, you will learn how to use the Oracle DROP TABLE statement to remove an existing table.
Introduction to Oracle DROP TABLE statement
To move a table to the recycle bin or remove it entirely from the database, you use the DROP TABLE statement:
Notice that the PURGE clause does not allow you to roll back or recover the table that you dropped. Therefore, it is useful if you don’t want the sensitive data to appear in the recycle bin.
Oracle DROP TABLE examples
Let’s look at some examples of using the DROP TABLE statement.
Basic Oracle DROP TABLE example
The following CREATE TABLE statement creates persons table for the demonstration:
The following example drops the persons table from the database:
Oracle DROP TABLE CASCADE CONSTRAINTS example
The following statements create two new tables named brands and cars :
In these tables, each brand has 1 or more cars while each car belongs to only one brand.
The following statement tries to drop the brands table:
Oracle issued the following error:
This is because the primary key of the brands table is currently referenced by the brand_id column in the cars table.
The following statement returns all foreign key constraints of the cars table:
To drop the brands table, you must use the CASCADE CONSTRAINTS clause as follows:
This statement dropped not only the brands table but also the foreign key constraint fk_brand from the cars table.
If you execute again the statement to get the foreign key constraints in the cars table, you will not see any row returned.
Oracle DROP TABLE PURGE example
The following statement drops the cars table using the PURGE clause:
Drop multiple tables at once
Oracle provides no direct way to drop multiple tables at once. However, you can use the following PL/SQL block to do it:
Then, execute the PL/SQL block above.
In this tutorial, you have learned how to use the Oracle DROP TABLE statement to drop a table from the database.
Cascade constraints oracle что это
Используя запрос DROP можно удалить таблицы (TABLE), индексы (INDEX) и базы данных (DATABASE).
DROP TABLE
DROP TABLE, применяемый в базе данных Oracle.
Обычно с таблицей в базе данных связано несколько объектов, например индекс, создаваемый первичным ключом, или ограничение UNIQUE, налагаемое на столбцы таблицы.
При удалении таблицы Oracle автоматически удаляет и любой связанный с ней индекс. Для удаления таблицы из БД необходимо выполнить команду DROP TABLE:
DROP TABLE Пример 1
Удаление таблицы:
Однако удалить таблицу не всегда столь просто. В любой момент мы можем создать таблицу с ограничениями целостности. Ограничение целостности (Integrityconstraint ) – это правило, устанавливаемое для таблицы и ограничивающее тип данных, которые можно вводить в эту таблицу. Если попытаться удалить таблицу с ограничениями целостности, возвращается сообщение об ошибке следующего вида: «Unique/primary keys in table referenced by foreign keys» (на уникальные/первичные ключи таблицы ссылаются внешние ключи).
Когда существуют ограничения для других таблиц, на которые ссылается удаляемая таблица, можно пользоваться каскадной конструкцией CASCADE CONSTRAINTS:
DROP TABLE. Пример 2
Удаление таблицы с ограничениями целостности:
DROP TABLE table CASCADE CONSTRAINTS;
DROP TABLE, применяемый в mySQL
DROP TABLE. Пример 3
Для удаления таблицы также используется запрос:
DROP TABLE. Пример 4
В случае, если необходимо установить проверку на существование таблицы при удалении (если существует удалить таблицу) запрос принимает следующий вид:
DROP TABLE IF EXISTS table;
Данный запрос будет выполнен в том случае, если удаляемая таблица существует в базе данных.
DROP INDEX
Данный запрос DROP INDEX используется для удаления индексов в таблице.
DROP INDEX, применяемый в базе данных Oracle:
Когда индекс в базе данных больше не нужен, разработчик может удалить его командой DROP INDEX. После удаления индекса эффективность поиска с использованием столбца или столбцов, ограниченных индексом, больше не повышается и упоминание об индексе исчезает из словаря данных. Индекс, применяемый для первичного ключа, удалить нельзя.
Синтаксис оператора DROP INDEX одинаков для удаления индекса любого типа (уникальности, битовой карты или В-дерева). Чтобы каким-то образом улучшить индекс, нужно сначала удалить его, а потом создать новый.
DROP INDEX. Пример 1
DROP INDEX my_index;
DROP INDEX, применяемый в mySQL:
DROP INDEX. Пример 2
Для удаления индексов (INDEX) используется запрос:
DROP INDEX my_index ON table;
Данный запрос удаляет индексы, указанные в my_index из таблицы table, но она не работает в версиях MySQL до 3.22. В версиях 3.22 и более поздних используется команда:
ALTER TABLE table_name DROP INDEX index_name;
DROP DATABASE
DROP DATABASE. Пример 1
DROP DATABASE database;
Запрос DROP DATABASE удаляет базу данных database.
TRUNCATE TABLE
Запрос TRUNCATE TABLE используется для того, чтобы удалить данные внутри таблицы, тем самым не затрагивая саму таблицу. В нужном случае TRUNCATE TABLE может быть очень полезен.
TRUNCATE TABLE, примеры использования TRUNCATE TABLE
TRUNCATE TABLE. Пример 1
TRUNCATE TABLE table;
DROP SEQUENCE
DROP SEQUENCE используется для удаления последовательности.
DROP SEQUENCE. Пример 1
DROP SEQUENCE sequence_name;
DROP SYNONYM
DROP SYNONYM используется для удаления синонимов.
DROP SYNONYM. Пример 1
DROP SYNONYM synonym_name;
Для удаления общих синонимов необходимо воспользоваться командой DROP PUBLIC SYNONYM.
DROP SYNONYM. Пример 2
DROP PUBLIC SYNONYM synonym_name;
Вы должны войти, чтобы оставить комментарий.
DROP TABLE
Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.
Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user’s space quota.
For an external table, this statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.
When you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, the table is purged from the recycle bin and can no longer be recovered with a FLASHBACK TABLE operation.
Dropping a table invalidates dependent objects and removes object privileges on the table. If you want to re-create the table, then you must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the table, and respecify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table.
CREATE TABLE and ALTER TABLE for information on creating and modifying tables
TRUNCATE and DELETE for information on removing data from a table
FLASHBACK TABLE for information on retrieving a dropped table from the recycle bin
The table must be in your own schema or you must have the DROP ANY TABLE system privilege.
Description of the illustration drop_table.gif
Specify the name of the table to be dropped. Oracle Database automatically performs the following operations:
All rows from the table are dropped.
All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, then any corresponding local index partitions are also dropped.
All the storage tables of nested tables and LOBs of table are dropped.
When you drop a range-, hash-, or list-partitioned table, then the database drops all the table partitions. If you drop a composite-partitioned table, then all the partitions and subpartitions are also dropped.
Oracle Database Administrator’s Guide for more information on dropping partitioned tables.
For an index-organized table, any mapping tables defined on the index-organized table are dropped.
For a domain index, the appropriate drop routines are invoked. Please refer to Oracle Data Cartridge Developer’s Guide for more information on these routines.
If any statistic types are associated with the table, then the database disassociates the statistics types with the FORCE clause and removes any user-defined statistics collected with the statistics type.
ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS for more information on statistics type associations
If the table is not part of a cluster, then the database returns all data blocks allocated to the table and its indexes to the tablespaces containing the table and its indexes.
To drop a cluster and all its the tables, use the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. See DROP CLUSTER.
If the table is a base table for a view, a container or master table of a materialized view, or if it is referenced in a stored procedure, function, or package, then the database invalidates these dependent objects but does not drop them. You cannot use these objects unless you re-create the table or drop and re-create the objects so that they no longer depend on the table.
If you choose to re-create the table, then it must contain all the columns selected by the subqueries originally used to define the materialized views and all the columns referenced in the stored procedures, functions, or packages. Any users previously granted object privileges on the views, stored procedures, functions, or packages need not be regranted these privileges.
If the table is a master table for a materialized view, then the materialized view can still be queried, but it cannot be refreshed unless the table is re-created so that it contains all the columns selected by the defining query of the materialized view.
If the table has a materialized view log, then the database drops this log and any other direct-path INSERT refresh information associated with the table.
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.
You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.
Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process. It also provides enhanced security if you want to prevent sensitive material from appearing in the recycle bin.
Oracle Database Administrator’s Guide for information on the recycle bin and naming conventions for objects in the recycle bin
Ограничения целостности. Представления данных
Выбор таблицы-адресата для внешнего ключа
Столбцам внешнего ключа не запрещено ссылаться на столбцы своей же таблицы:
В стандарте SQL такой внешний ключ называется рекурсивным.
Равным образом внешнему ключу разрешено ссылаться на столбцы таблицы из другой схемы. Только в этом случае потребуется иметь на таблицу из другой схемы привилегию REFERENCES :
Обратите внимание, что привилегии на SELECT к таблице-адресату в случае нахождения последней в иной схеме не требуется.
Пример использования такой возможности — поддержка в разных схемах ссылок на справочные таблицы, собранные вместе в отдельную схему. Правда, при таком подходе внесение изменений в БД потребует дополнительного внимания.
Поведение СУБД при попытке удалить «родительскую» запись
Обычное ограничение типа «внешний ключ» запрещает СУБД удалять родительскую запись, если на нее существуют в данный момент ссылки:
Однако можно смоделировать и иную реакцию СУБД, разрешив-таки удаление родительской записи.
Указание ON DELETE CASCADE в определении ключа приведет заодно с удалением родительской записи к автоматическому удалению подчиненных записей:
При наличии цепочки так определенных внешних ключей автоматическое удаление будет распространяться по цепочке:
Автоматическим удалением по цепочке следует пользоваться с осторожностью.
Указание ON DELETE SET NULL в определении ключа приведет заодно с удалением родительской записи к автоматическому удалению значений в полях-ссылках подчиненных записей:
Строка в таблице Z пропала, а в таблице W осталась (проверьте!).
Обратите внимание, что фраза CASCADE CONSTRAINTS в предложении DROP TABLE не соответствует ни первому, ни второму из вышеприведенных вариантов, попросту удаляя ограничение типа «внешний ключ» и не трогая значений подчиненных записей:
Дополнительное условие для занесения значений в поля строки
Пример указания условных проверок при создании таблицы:
Упражнение. Попробуйте выполнить:
Обратите внимание, что формулировать ограничения целостности в тексте предложения CREATE TABLE допускается как на уровне столбца (если только ограничение касается единственного столбца, как, например, PK_EMP1 и CK_SAL ), так и на уровне таблицы ( CHECK_WHOLE_EARNING и SALE_COMMISSION ). Такая разница в записи не отражается на свойствах таблицы.
Упражнение. Удалите созданную таблицу EMP1 и создайте заново, но сформулировав те же ограничения целостности на уровне таблицы.
В отличие от стандарта SQL, в Oracle условное выражение в CHECK не имеет право содержать обращения к БД (в том числе через посредство функций), что существенно ослабляет его общую силу.
