Как удалить constraint oracle
Constraints are the ways to enforce data integrity rules in the Oracle database. If we dont require constraint at any point of time, we can drop constraint in Oracle using the alter table command. Lets check with the addition of constraint and then dropping them
Oracle Table creation and Constraint creation
Here we have created two tables and created constraints for primary key, foreign key ,check constraint.Now that we have created the constraints,lets see how we can drop the constraint in oracle
Drop the constraint can be simply done by
We can drop primary key constraint, unique key constraint, foreign key constraint , check constraint and non null constraint using the same command given above. We just need to first find all the constraints on the table and then drop them according to the need.
Table of Contents
drop foreign key constraint oracle
drop primary key constraint in oracle
We can also drop the Primary key constraint by specifying the column name
Important Note
You cannot drop a primary key or unique key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the CASCADE clause. If you omit CASCADE, then Oracle Database does not drop the primary key or unique constraint if any foreign key references it.
Drop Not null or check constraints
Now Dropping the Not Null constraints
drop unique constraint oracle
Now the table is created, we can drop the constraint using the below statement
We can drop the unique constraint by specifying the column name also
Drop all the constraint in Oracle
You can drop all the constraints in the table using the below procedure also
We learn how to drop constraint in oracle. The constraint could be primary key, Unique key, foreign key and check constraint.
I hope you like this article and it helps in your day to day working. Please let me know the feedback on this.
Ограничение PRIMARY KEY однозначно идентифицирует каждую запись в таблице.
Первичные ключи должны содержать уникальные значения и не могут содержать нулевые значения.
Таблица может иметь только один первичный ключ, а в таблице этот первичный ключ может состоять из одного или нескольких столбцов (полей).
SQL PRIMARY KEY в CREATE TABLE
Следующий SQL создает первичный ключ о "ID" в столбик, когда таблица "Persons" создается:
CREATE TABLE Persons (ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Чтобы разрешить именование ограничения первичного ключа и определить ограничение первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Примечание: В приведенном выше примере существует только один первичный ключ (PK_Person). Однако значение первичного ключа состоит из двух столбцов (ID + LastName).
SQL PRIMARY KEY в ALTER TABLE
Чтобы создать ограничение первичного ключа для столбца "ID", когда таблица уже создана, используйте следующий SQL:
MySQL / SQL Server / Oracle / MS Access:
Чтобы разрешить именование ограничения первичного ключа и определить ограничение первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE PersonsADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Примечание: Если вы используете инструкцию ALTER TABLE для добавления первичного ключа, то столбец(ы) первичного ключа должен(ы) быть уже объявлено, что они не содержат нулевых значений (при первом создании таблицы).
Команда ADD CONSTRAINT используется для создания ограничения после того, как таблица уже создана.
Следующий SQL добавляет ограничение с именем "PK_Person", которое является ограничением первичного ключа для нескольких столбцов (ID и фамилия):
Пример
ALTER TABLE PersonsADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
DROP CONSTRAINT
Команда DROP CONSTRAINT используется для удаления уникального, первичного ключа, внешнего ключа или ограничения проверки.
Отбросьте уникальное ограничение
Чтобы удалить уникальное ограничение, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Отбросьте ограничение первичного ключа
Чтобы удалить ограничение первичного ключа, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Удаления ограничения внешнего ключа
Чтобы удалить ограничение внешнего ключа, используйте следующий SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE OrdersDROP FOREIGN KEY FK_PersonOrder;
Отбросьте ограничение проверки
Чтобы удалить ограничение проверки, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Упражнения
Тесты
КАК СДЕЛАТЬ
ПОДЕЛИТЬСЯ
СЕРТИФИКАТЫ
Сообщить об ошибке
Если вы хотите сообщить об ошибке или сделать предложение, не стесняйтесь, присылайте нам электронное письмо:
Ваше предложение:
Спасибо, за вашу помощь!
Топ Учебники
Топ Справочники
Топ Примеры
Веб Сертификаты
SchoolsW3 оптимизирован для обучения, тестирования и тренировки. Примеры упрощают и улучшают чтение и базовое понимание. Учебники, справочники, примеры постоянно пересматриваются, для того, чтобы избежать ошибки, невозможно гарантировать правильность всего содержимого. Используя данный сайт, вы соглашаетесь прочитать и принять условия использования, cookie и Политика конфиденциальности. Авторское право 1999 - 2021 Все права защищены.
Работает на W3.CSS.
Integrity constraints are rules that restrict the values for one or more columns in a table. Constraint clauses can appear in either CREATE TABLE or ALTER TABLE statements, and identify the column or columns affected by the constraint and identify the conditions of the constraint.
This section discusses the concepts of constraints and identifies the SQL statements used to define and manage integrity constraints. The following topics are contained in this section:
Oracle Database Concepts for a more thorough discussion of integrity constraints
Oracle Database Advanced Application Developer's Guide for detailed information and examples of using integrity constraints in applications
Integrity Constraint States
You can specify that a constraint is enabled ( ENABLE ) or disabled ( DISABLE ). If a constraint is enabled, data is checked as it is entered or updated in the database, and data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform can be allowed to enter the database.
Additionally, you can specify that existing data in the table must conform to the constraint ( VALIDATE ). Conversely, if you specify NOVALIDATE , you are not ensured that existing data conforms.
An integrity constraint defined on a table can be in one of the following states:
For details about the meaning of these states and an understanding of their consequences, see the Oracle Database SQL Language Reference. Some of these consequences are discussed here.
Disabling Constraints
To enforce the rules defined by integrity constraints, the constraints should always be enabled. However, consider temporarily disabling the integrity constraints of a table for the following performance reasons:
When loading large amounts of data into a table
When performing batch operations that make massive changes to a table (for example, changing every employee's number by adding 1000 to the existing number)
When importing or exporting one table at a time
In all three cases, temporarily disabling integrity constraints can improve the performance of the operation, especially in data warehouse configurations.
It is possible to enter data that violates a constraint while that constraint is disabled. Thus, you should always enable the constraint after completing any of the operations listed in the preceding bullet list.
Enabling Constraints
While a constraint is enabled, no row violating the constraint can be inserted into the table. However, while the constraint is disabled such a row can be inserted. This row is known as an exception to the constraint. If the constraint is in the enable novalidated state, violations resulting from data entered while the constraint was disabled remain. The rows that violate the constraint must be either updated or deleted in order for the constraint to be put in the validated state.
You can identify exceptions to a specific integrity constraint while attempting to enable the constraint. See "Reporting Constraint Exceptions". All rows violating constraints are noted in an EXCEPTIONS table, which you can examine.
Enable Novalidate Constraint State
When a constraint is in the enable novalidate state, all subsequent statements are checked for conformity to the constraint. However, any existing data in the table is not checked. A table with enable novalidated constraints can contain invalid data, but it is not possible to add new invalid data to it. Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data.
Enabling a constraint does not require validation. Enabling a constraint novalidate is much faster than enabling and validating a constraint. Also, validating a constraint that is already enabled does not require any DML locks during validation (unlike validating a previously disabled constraint). Enforcement guarantees that no violations are introduced during the validation. Hence, enabling without validating enables you to reduce the downtime typically associated with enabling a constraint.
Efficient Use of Integrity Constraints: A Procedure
Using integrity constraint states in the following order can ensure the best benefits:
Perform the operation (load, export, import).
Enable novalidate state.
No locks are held.
All constraints can go to enable state concurrently.
Constraint enabling is done in parallel.
Concurrent activity on table is permitted.
Setting Integrity Constraints Upon Definition
When an integrity constraint is defined in a CREATE TABLE or ALTER TABLE statement, it can be enabled, disabled, or validated or not validated as determined by your specification of the ENABLE / DISABLE clause. If the ENABLE / DISABLE clause is not specified in a constraint definition, the database automatically enables and validates the constraint.
Disabling Constraints Upon Definition
The following CREATE TABLE and ALTER TABLE statements both define and disable integrity constraints:
An ALTER TABLE statement that defines and disables an integrity constraint never fails because of rows in the table that violate the integrity constraint. The definition of the constraint is allowed because its rule is not enforced.
Enabling Constraints Upon Definition
The following CREATE TABLE and ALTER TABLE statements both define and enable integrity constraints:
An ALTER TABLE statement that defines and attempts to enable an integrity constraint can fail because rows of the table violate the integrity constraint. If this case, the statement is rolled back and the constraint definition is not stored and not enabled.
When you enable a UNIQUE or PRIMARY KEY constraint an associated index is created.
An efficient procedure for enabling a constraint that can make use of parallelism is described in "Efficient Use of Integrity Constraints: A Procedure".
Modifying, Renaming, or Dropping Existing Integrity Constraints
You can use the ALTER TABLE statement to enable, disable, modify, or drop a constraint. When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled , the index is dropped, unless you specify otherwise.
While enabled foreign keys reference a PRIMARY or UNIQUE key, you cannot disable or drop the PRIMARY or UNIQUE key constraint or the index.
Disabling Enabled Constraints
The following statements disable integrity constraints. The second statement specifies that the associated indexes are to be kept.
The following statements enable novalidate disabled integrity constraints:
The following statements enable or validate disabled integrity constraints:
The following statements enable disabled integrity constraints:
To disable or drop a UNIQUE key or PRIMARY KEY constraint and all dependent FOREIGN KEY constraints in a single step, use the CASCADE option of the DISABLE or DROP clauses. For example, the following statement disables a PRIMARY KEY constraint and any FOREIGN KEY constraints that depend on it:
Renaming Constraints
The ALTER TABLE. RENAME CONSTRAINT statement enables you to rename any currently existing constraint for a table. The new constraint name must not conflict with any existing constraint names for a user.
The following statement renames the dname_ukey constraint for table dept :
When you rename a constraint, all dependencies on the base table remain valid.
The RENAME CONSTRAINT clause provides a means of renaming system generated constraint names.
Dropping Constraints
You can drop an integrity constraint if the rule that it enforces is no longer true, or if the constraint is no longer needed. You can drop the constraint using the ALTER TABLE statement with one of the following clauses:
DROP PRIMARY KEY
The following two statements drop integrity constraints. The second statement keeps the index associated with the PRIMARY KEY constraint:
If FOREIGN KEY s reference a UNIQUE or PRIMARY KEY , you must include the CASCADE CONSTRAINTS clause in the DROP statement, or you cannot drop the constraint.
Deferring Constraint Checks
When the database checks a constraint, it signals an error if the constraint is not satisfied. You can defer checking the validity of constraints until the end of a transaction.
When you issue the SET CONSTRAINTS statement, the SET CONSTRAINTS mode lasts for the duration of the transaction, or until another SET CONSTRAINTS statement resets the mode.
You cannot issue a SET CONSTRAINT statement inside a trigger.
Deferrable unique and primary keys must use nonunique indexes.
Set All Constraints Deferred
Within the application being used to manipulate the data, you must set all constraints deferred before you actually begin processing any data. Use the following DML statement to set all deferrable constraints deferred:
The SET CONSTRAINTS statement applies only to the current transaction. The defaults specified when you create a constraint remain as long as the constraint exists. The ALTER SESSION SET CONSTRAINTS statement applies for the current session only.
Check the Commit (Optional)
You can check for constraint violations before committing by issuing the SET CONSTRAINTS ALL IMMEDIATE statement just before issuing the COMMIT . If there are any problems with a constraint, this statement fails and the constraint causing the error is identified. If you commit while constraints are violated, the transaction is rolled back and you receive an error message.
Reporting Constraint Exceptions
If exceptions exist when a constraint is validated, an error is returned and the integrity constraint remains novalidated. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot validate the constraint until all exceptions to the constraint are either updated or deleted.
To determine which rows violate the integrity constraint, issue the ALTER TABLE statement with the EXCEPTIONS option in the ENABLE clause. The EXCEPTIONS option places the rowid, table owner, table name, and constraint name of all exception rows into a specified table.
You must create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause before enabling the constraint. You can create an exception table by executing the UTLEXCPT.SQL script or the UTLEXPT1.SQL script.
Your choice of script to execute for creating the EXCEPTIONS table is dependent upon the type of table you are analyzing. See the Oracle Database SQL Language Reference for more information.
Both of these scripts create a table named EXCEPTIONS . You can create additional exceptions tables with different names by modifying and resubmitting the script.
The following statement attempts to validate the PRIMARY KEY of the dept table, and if exceptions exist, information is inserted into a table named EXCEPTIONS :
If duplicate primary key values exist in the dept table and the name of the PRIMARY KEY constraint on dept is sys_c00610 , then the following query will display those exceptions:
The following exceptions are shown:
A more informative query would be to join the rows in an exception report table and the master table to list the actual rows that violate a specific constraint, as shown in the following statement and results:
All rows that violate a constraint must be either updated or deleted from the table containing the constraint. When updating exceptions, you must change the value violating the constraint to a value consistent with the constraint or to a null. After the row in the master table is updated or deleted, the corresponding rows for the exception in the exception report table should be deleted to avoid confusion with later exception reports. The statements that update the master table and the exception report table should be in the same transaction to ensure transaction consistency.
To correct the exceptions in the previous examples, you might issue the following transaction:
When managing exceptions, the goal is to eliminate all exceptions in your exception report table.
While you are correcting current exceptions for a table with the constraint disabled, it is possible for other users to issue statements creating new exceptions. You can avoid this by marking the constraint ENABLE NOVALIDATE before you start eliminating exceptions.
Oracle Database Reference for a description of the EXCEPTIONS table
Viewing Constraint Information
Oracle Database provides the following views that enable you to see constraint definitions on tables and to identify columns that are specified in constraints:
Oracle Database Reference contains descriptions of the columns in these views
Читайте также: