This chapter explains how to enforce the business rules connected with your database and also prevent the entry of invalid information into tables by utilizing integrity constraints. Topics include the following:

Overview of verity Constraints

You can define integrity constraints to enforce service rules on data in her tables. Organization rules clues conditions and also relationships the must constantly be true, or must constantly be false. Since each firm defines its own policies around things choose salaries, employee numbers, perform tracking, and also so on, you can specify a different set of rules because that each database table.

You are watching: Which of the following statements about creating constraints is incorrect?

When an verity constraint applies to a table, all data in the table should conform come the equivalent rule. When you worry a SQL statement that modifies data in the table, ensures the the new data satisfies the truth constraint, without the should do any type of checking within her program.

When come Enforce company Rules through Integrity Constraints

You can enforce rules by defining integrity constraints more reliably than by adding logic to her application. can examine that every the data in a table obeys an verity constraint quicker than an applications can.

example of an truth Constraint because that a company Rule

To ensure that each employee works for a precious department, an initial create a ascendancy that all values in the department table are distinctive :

ALTER TABLE Dept_tab add PRIMARY an essential (Deptno); Then, produce a rule that every department detailed in the employee table must complement one of the worths in the department table:

ALTER TABLE Emp_tab add FOREIGN crucial (Deptno) recommendations Dept_tab(Deptno); as soon as you add a new employee record to the table, immediately checks that its room number appears in the room table.

To obtrude this dominance without verity constraints, you can use a cause to questions the department table and also test that each new employee\"s room is valid. Yet this technique is much less reliable 보다 the verity constrain, because SELECT in supplies \"consistent read\" and also so the query might miss uncommitted alters from various other transactions.

When to Enforce service Rules in Applications

You might enforce company rules v application logic and also through truth constraints, if you can filter out negative data before attempting an insert or update. This might let you carry out instant feedback come the user, and reduce the fill on the database. This method is proper when you deserve to determine the data values room wrong or the end of range, there is no checking against any data currently in the table.

Creating Indexes because that Use with Constraints

All allowed unique and also primary secrets require corresponding indexes. Friend should produce these indexes through hand, quite than letting the database produce them because that you. Keep in mind that:

You should practically always index foreign keys, and the database go not do this because that you.

When to usage NOT NULL verity Constraints

By default, all columns deserve to contain nulls. Only define NOT NULL constraints for columns that a table the absolutely require values at every times.

For example, a new employee\"s manager or rental date can be temporarily omitted. Part employees can not have actually a commission. Columns choose these have to not have NOT NULL truth constraints. However, one employee name could be required from the really beginning, and you deserve to enforce this ascendancy with a not NULL integrity constraint.

NOT NULL constraints space often linked with other species of truth constraints to further restrict the values that have the right to exist in certain columns of a table. Use the mix of not NULL and also UNIQUE key integrity limit to force the intake of worths in the distinct key; this mix of data integrity rule eliminates the possibility that any new row\"s data will ever attempt to conflict with an currently row\"s data.

Because indexes execute not store tricks that space all null, if you desire to permit index-only scans that the table or some other operation that needs indexing all rows, put a not NULL constraint on at least one indexing column.

A no NULL constraint is specified prefer this:

ALTER TABLE emp modify ename no NULL;

Figure 4-1 Table v NOT NULL integrity Constraints
Text summary of the illustration adg81038.gif

When to use Default shaft Values

Assign default values to columns the contain a typical value. For example, in the DEPT_TAB table, if most departments are situated at one site, climate the default value for the LOC column can be set to this value (such as brand-new YORK).

Default values can aid avoid errors wherein there is a number, such together zero, that uses to a obelisk that has actually no entry. For example, a default worth of zero can simplify testing, by an altering a test like this:

IF sal IS no NULL and also sal come the much easier form:

IF sal depending on your organization rules, you might use default values to represent zero or false, or leaving the default worths as NULL to denote an unknown value.

Defaults are additionally useful when you use a check out to do a subset of a table\"s columns visible. For example, friend might permit users come insert rows v a view. The basic table might also have a column named INSERTER, not contained in the an interpretation of the view, to log the user the inserts each row. To document the user surname automatically, define a default worth that phone call the USER function:

CREATE TABLE audit_trail( value1 NUMBER, value2 VARCHAR2(32), inserter VARCHAR2(30) DEFAULT USER);
See Also: For one more example the assigning a default pillar value, describe the section \"Creating Tables\".

Setting Default pillar Values

Default values have the right to include any type of literal, or almost any expression, including calls to SYSDATE, SYS_CONTEXT, USER, USERENV, and UID. Default worths cannot incorporate expressions that refer to a sequence, PL/SQL function, column, LEVEL, ROWNUM, or PRIOR. The datatype the a default literal meaning or expression must enhance or be convertible to the column datatype.

Sometimes the default value is the result of a SQL function. Because that example, a call to SYS_CONTEXT can set a various default worth depending on problems such as the user name. To be used as a default value, a SQL role must have parameters that are all literals, can not reference any type of columns, and also cannot call any kind of other functions.

If you do not explicitly define a default worth for a column, the default because that the pillar is implicitly collection to NULL.

You deserve to use the keyword DEFAULT within an INSERT statement rather of a literal value, and the corresponding default worth is inserted.

Figure 4-2 Table v a UNIQUE an essential Constraint
Text description of the illustration adg81039.gif

Choosing a Table\"s major Key

Each table can have one main key, which unique identifies each heat in a table and also ensures the no duplicate rows exist. Use the following guidelines when picking a major key:

When to usage UNIQUE key Integrity Constraints

Choose columns for distinct keys carefully. The objective of these contraints is various from the of main keys. Unique key constraints are appropriate for any column wherein duplicate values room not allowed. Major keys determine each heat of the table uniquely, and also typically contain values that have actually no meaning other 보다 being unique.


Although UNIQUE vital constraints allow null values, you cannot have identical worths in the non-null columns that a composite UNIQUE crucial constraint.

Some examples of an excellent unique tricks include:

Constraints on Views for Performance, no Data Integrity

The constraints debated throughout this chapter apply to tables, no views.

Although you can declare border on views, such constraints carry out not aid maintain data integrity. Instead, lock are used to enable query rewrites top top queries entailing views, i m sorry helps power with manifested views and also other data warehousing features. Such constraints are always declared v the DISABLE keyword, and also you cannot use the VALIDATE keyword. The border are never ever enforced, and there is no linked index.

See Also:

medtox.org9i Data Warehousing Guide for details on query rewrite, manifested views, and also the performance factors for proclaiming constraints on views.

Enforcing Referential Integrity with Constraints

Whenever two tables save one or an ext common columns, have the right to enforce the relationship in between the 2 tables through a referential integrity constraint. Specify a main or UNIQUE vital constraint on the shaft in the parental table (the one that has actually the complete set of pillar values). Define a FOREIGN an essential constraint top top the shaft in the child table (the one whose values must refer come existing worths in the other table).

See Also:

Depending on this relationship, you may want come define added integrity constraints consisting of the international key, as provided in the section \"Defining Relationships between Parent and also Child Tables\".

Figure4-3 shows a foreign vital defined top top the room number. It guarantees that every worth in this shaft must enhance a value in the primary an essential of the department table. This constraint stays clear of erroneous department numbers from gaining into the employee table.

Foreign keys deserve to be made up of lot of columns. Such a composite international key have to reference a composite main or unique an essential of the precise same structure, through the same variety of columns and the same datatypes. Since composite primary and also unique secrets are restricted to 32 columns, a composite foreign key is also restricted to 32 columns.

About Nulls and Foreign Keys

Foreign keys allow vital values that space all null, also if there space no matching PRIMARY or distinct keys.

Figure 4-3 Tables with Referential truth Constraints
Text description of the illustration adg81040.gif

Defining Relationships between Parent and also Child Tables

Several relationships between parent and child tables deserve to be established by the other varieties of truth constraints characterized on the foreign vital in the son table.

No constraints on the Foreign an essential

When no various other constraints are characterized on the foreign key, any number of rows in the son table deserve to reference the exact same parent vital value. This model permits nulls in the international key.

This model creates a \"one-to-many\" relationship in between the parent and foreign secrets that allows undetermined worths (nulls) in the international key. An instance of together a partnership is shown in number 4-3 on page 8 in between the employee and department tables. Each department (parent key) has numerous employees (foreign key), and also some employees can not be in a department (nulls in the foreign key).

no NULL Constraint ~ above the Foreign vital

When nulls are not permitted in a international key, each row in the boy table must explicitly reference a value in the parent an essential because nulls are not enabled in the foreign key. However, any variety of rows in the boy table have the right to reference the very same parent an essential value.

This model establishes a \"one-to-many\" relationship between the parent and foreign keys. However, each row in the boy table must have a reference to a parent key value; the lack of a value (a null) in the foreign key is no allowed. The same example in the previous section can be provided to illustrate such a relationship. However, in this case, employee must have a referral to a certain department.

distinctive Constraint on the Foreign an essential

When a unique constraint is defined on the foreign key, one row in the kid table have the right to reference a parent key value. This model enables nulls in the foreign key.

This model establishes a \"one-to-one\" relationship in between the parent and also foreign tricks that enables undetermined values (nulls) in the foreign key. Because that example, assume the the employee table had a column named MEMBERNO, referring to an employee\"s membership number in the company\"s insurance money plan. Also, a table called INSURANCE has a primary vital named MEMBERNO, and also other columns of the table save respective info relating to an employee\"s insurance money policy. The MEMBERNO in the employee table need to be both a foreign an essential and a distinct key:

UNIQUE and NOT NULL border on the Foreign crucial

When both UNIQUE and NOT NULL constraints are identified on the international key, just one row in the boy table have the right to reference a parent key value. Due to the fact that nulls are not permitted in the international key, each row in the son table must explicitly reference a value in the parental key.

This model creates a \"one-to-one\" relationship between the parent and foreign keys that does not permit undetermined values (nulls) in the foreign key. If you increase the previous example by adding a not NULL constraint top top the MEMBERNO shaft of the employee table, in enhancement to guaranteeing that each employee has a unique membership number, you additionally ensure that no undetermined worths (nulls) are permitted in the MEMBERNO pillar of the employee table.

Rules because that Multiple FOREIGN crucial Constraints allows a shaft to it is in referenced by lot of FOREIGN key constraints; effectively, there is no border on the number of dependent keys. This case might be present if a solitary column is component of two various composite foreign keys.

Deferring Constraint Checks

When check a constraint, it signal an error if the constraint is not satisfied. You can use the set CONSTRAINTS statement to defer check the validity of constraints till the end of a transaction.

The set CONSTRAINTS setting lasts because that the expression of the transaction, or till another collection CONSTRAINTS declare resets the mode.

Guidelines because that Deferring Constraint check Select appropriate Data

You may wish to defer constraint check on UNIQUE and also FOREIGN secrets if the data you are working with has any kind of of the following characteristics:

When handle with mass data being manipulated by external applications, you have the right to defer checking constraints because that validity till the end of a transaction.

for sure Constraints Are produced Deferrable

After you have actually identified and also selected the proper tables, make certain their FOREIGN, UNIQUE and PRIMARY key constraints are produced deferrable. You can do for this reason by issuing a statement similar to the following:

CREATE TABLE dept ( deptno NUMBER primary KEY, dname VARCHAR2 (30) );CREATE TABLE emp ( empno NUMBER, ename VARCHAR2 (30), deptno NUMBER referrals (dept), CONSTRAINT epk PRIMARY crucial (empno) DEFERRABLE, CONSTRAINT efk FOREIGN an essential (deptno) REFERENCES (dept.deptno) DEFERRABLE);INSERT right into dept values (10, \"Accounting\");INSERT right into dept worths (20, \"SALES\");INSERT into emp worths (1, \"Corleone\", 10);INSERT right into emp worths (2, \"Costanza\", 20);COMMIT;SET CONSTRAINT efk DEFERRED;UPDATE dept collection deptno = deptno + 10 where deptno = 20;SELECT * indigenous emp order BY deptno;EMPNO ENAME DEPTNO----- -------------- ------- 1 Corleone 10 2 Costanza 20UPDATE emp set deptno = deptno + 10 whereby deptno = 20;SELECT * indigenous emp bespeak BY deptno;EMPNO ENAME DEPTNO----- -------------- ------- 1 Corleone 10 2 Costanza 30COMMIT; collection All constraints Deferred in ~ the application that manipulates the data, you must collection all limit deferred before you start processing any kind of data. Use the following DML explain to set all deferrable border deferred:

Note: The set CONSTRAINTS statement uses only to the current transaction. The defaults specified once you produce a constraint stay as lengthy as the constraint exists. The transform SESSION collection CONSTRAINTS statement applies for the existing session only.

examine the go (Optional)

You can check for constraint violations prior to committing through issuing the collection CONSTRAINTS ALL immediate statement just prior to issuing the COMMIT. If there are any kind of problems v a constraint, this statement will fail and the constraint resulting in the error will be identified. If you commit while constraints are violated, the transaction will be rolled earlier and you will receive an error message.

Managing Constraints that Have connected Indexes

When you produce a distinct or major key, checks to check out if an currently index can be offered to force uniqueness for the constraint. If over there is no such index, create one.

Minimizing room and Time Overhead because that Indexes connected with Constraints

When uses a distinctive index come enforce a constraint, and also constraints associated with the distinct index are dropped or disabled, the index is dropped. To keep the statistics associated with the index, or if it would certainly take a lengthy time come re-create it, you can specify the save INDEX clause on the fall command for the constraint.

While permitted foreign tricks reference a major or distinctive key, you cannot disable or autumn the main or UNIQUE key constraint or the index.

To reuse present indexes when developing unique and also primary key constraints, girlfriend can include USING table of contents in the constraint clause. Fpr example:

CREATE TABLE b( b1 INTEGER, b2 INTEGER, CONSTRAINT unique1 (b1, b2) making use of INDEX (CREATE distinctive INDEX b_index top top b(b1, b2), CONSTRAINT unique2 (b1, b2) utilizing INDEX b_index);

Guidelines for Indexing international Keys

you should almost always index international keys. The only exception is when the matching unique or primary crucial is never ever updated or deleted.

See Also:

medtox.org9i Database Concepts for information on locking mechanisms entailing indexes and keys.

About Referential truth in a spread Database

The statements of a referential verity constraint cannot specify a foreign vital that references a major or unique an essential of a far table.

However, you deserve to maintain parent/child table relationships across nodes making use of triggers.

See Also:

For much more information about triggers the enforce referential integrity, refer to Chapter15, \"Using Triggers\".


If you decision to define referential integrity throughout the nodes that a spread database utilizing triggers, be aware that network failures deserve to make both the parent table and the kid table inaccessible. Because that example, assume the the kid table is in the SALES database, and the parent table is in the HQ database.

If the network connection in between the two databases fails, then part DML statements against the son table (those that insert rows or update a foreign an essential value) cannot proceed, because the referential truth triggers have to have accessibility to the parental table in the HQ database.

When to Use inspect Integrity Constraints

Use examine constraints as soon as you have to enforce verity rules based on logical expressions, such as comparisons. Never ever use examine constraints when any type of of the other varieties of truth constraints can carry out the essential checking.

Examples of inspect constraints include the following:

Restrictions on check Constraints

A check integrity constraint requires that a problem be true or unknown because that every heat of the table. If a statement reasons the condition to evaluate to false, climate the explain is rolling back. The problem of a examine constraint has the adhering to limitations:

Designing inspect Constraints

When using inspect constraints, remember that a check constraint is violated just if the problem evaluates come false; true and unknown values (such as comparisons v nulls) do not violate a inspect condition. Make sure that any type of CHECK constraint the you specify is details enough come enforce the rule.

For example, think about the following inspect constraint:

CHECK (Sal > 0 OR Comm >= 0) At first glance, this rule may be understood as \"do not enable a heat in the employee table unless the employee\"s salary is greater than zero or the employee\"s the supervisory board is greater than or same to zero.\" however if a heat is placed with a null salary, that heat does no violate the examine constraint regardless of whether the commission value is valid, because the entire examine condition is evaluated together unknown. In this case, you have the right to prevent together violations by placing not NULL integrity constraints top top both the SAL and COMM columns.


If you space not sure once unknown values result in NULL conditions, testimonial the truth tables because that the reasonable operators and also and OR in medtox.org9i SQL Reference

Rules for Multiple inspect Constraints

A single column can have multiple examine constraints that referral the pillar in the definition. Over there is no limit to the variety of CHECK border that can be characterized that recommendation a column.

The order in i beg your pardon the constraints room evaluated is no defined, for this reason be careful not to depend on the order or to specify multiple border that problem with every other.

Choosing in between CHECK and NOT NULL truth Constraints

According to the ANSI/ISO standard, a not NULL truth constraint is an instance of a check integrity constraint, whereby the condition is the following:

CHECK (Column_name IS no NULL) Therefore, not NULL integrity constraints because that a solitary column can, in practice, be written in two forms: making use of the not NULL constraint or a examine constraint. Because that ease that use, you should always choose to specify NOT NULL truth constraints, instead of inspect constraints v the IS no NULL condition.

In the case where a composite crucial can enable only all nulls or all values, you should use a examine integrity constraint. For example, the complying with expression of a examine integrity constraint permits a crucial value in the composite key made up of columns C1 and also C2 come contain either all nulls or all values:

CHECK ((C1 IS NULL and C2 IS NULL) OR (C1 IS no NULL and C2 IS no NULL))

Examples of defining Integrity Constraints

here are some examples showing how to create straightforward constraints during the prototype step of your database design.

Notice just how all border are provided a name. Naming the constraints prevents the database from developing multiple duplicates of the same constraint, with various system-generated names, if the DDL is operation multiple times.

See Also:

medtox.org9i Database Administrator\"s Guide for information on creating and also maintaining constraints for a big production database.

Defining verity Constraints through the develop TABLE Command: Example

The following examples of produce TABLE statements display the meaning of several integrity constraints:

CREATE TABLE Dept_tab ( Deptno NUMBER(3) CONSTRAINT Dept_pkey main KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT Dname_ukey distinctive (Dname, Loc), CONSTRAINT Loc_check1 examine (loc IN (\"NEW YORK\", \"BOSTON\", \"CHICAGO\")));CREATE TABLE Emp_tab ( Empno NUMBER(5) CONSTRAINT Emp_pkey main KEY, Ename VARCHAR2(15) not NULL, task VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT Mgr_fkey references Emp_tab, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) no NULL CONSTRAINT dept_fkey references Dept_tab ~ above DELETE CASCADE);

Defining Constraints v the change TABLE Command: Example

you can likewise define truth constraints utilizing the constraint clause of the transform TABLE command. For example, the following examples of change TABLE statements present the an interpretation of numerous integrity constraints:

CREATE distinct INDEX I_dept top top Dept_tab(deptno);ALTER TABLE Dept_tab add CONSTRAINT Dept_pkey PRIMARY key (deptno); change TABLE Emp_tab include CONSTRAINT Dept_fkey FOREIGN crucial (Deptno) references Dept_tab;ALTER TABLE Emp_tab change (Ename VARCHAR2(15) not NULL); you cannot develop a validated constraint on a table if the table already contains any type of rows that would certainly violate the constraint.

Privileges compelled to develop Constraints

The creator the a constraint must have the capacity to develop tables (the create TABLE or CREATE any type of TABLE mechanism privilege), or the capability to change the table (the alter object privilege for the table or the ALTER any type of TABLE device privilege) through the constraint. Additionally, UNIQUE and also PRIMARY key integrity constraints call for that the owner the the table have either a quota for the tablespace that includes the associated index or the limitless TABLESPACE mechanism privilege. FOREIGN vital integrity constraints also require some additional privileges.

Naming verity Constraints

Assign names to not NULL, unique KEY, major KEY, international KEY, and CHECK constraints making use of the CONSTRAINT alternative of the constraint clause. This name need to be distinctive with respect to other constraints the you own. If you perform not clues a constraint name, one is assigned through

Picking your very own name provides error messages because that constraint violations much more understandable, and prevents the production of multiple constraints if the SQL statements space run much more than once.

See the previous instances of the create TABLE and also ALTER TABLE declaration for instances of the CONSTRAINT alternative of the constraint clause. Note that the surname of each constraint is contained with various other information about the constraint in the data dictionary.

Enabling and Disabling integrity Constraints

This section explains the mechanisms and also procedures for manually enabling and disabling integrity constraints.

enabled constraint. As soon as a constraint is enabled, the matching rule is enforced on the data worths in the associated columns. The definition of the constraint is stored in the data dictionary.

disabled constraint. When a constraint is disabled, the equivalent rule is no enforced. The definition of the constraint is still stored in the data dictionary.

An truth constraint represents an assertion about the data in a database. This delinquent is constantly true as soon as the constraint is enabled. The assertion might or may not it is in true as soon as the constraint is disabled, since data that violates the verity constraint can be in the database.

Why Disable Constraints?

During day-to-day operations, constraints should constantly be enabled. In certain situations, in the interim disabling the verity constraints of a table provides sense for power reasons. For example:

Turning off integrity limit temporarily increases these operations.

around Exceptions come Integrity constraints

If a row of a table disobeys an integrity constraint, climate this heat is in violation that the constraint and is called an exception come the constraint. If any exceptions exist, then the constraint cannot be enabled. The rows the violate the constraint have to be either updated or deleted prior to the constraint can be enabled.

You deserve to identify exceptions for a certain integrity constraint together you shot to enable the constraint.

enabling Constraints

When you define an integrity constraint in a create TABLE or change TABLE statement, automatically enables the constraint by default. For code clarity, you have the right to explicitly permit the constraint by consisting of the enable clause in that definition.

Use this technique when producing tables that begin off empty, and also are occupied a heat at a time by separation, personal, instance transactions. In such cases, you desire to ensure the data are regular at all times, and also the power overhead of every DML procedure is small.

The following produce TABLE and also ALTER TABLE statements both define and allow integrity constraints:

CREATE TABLE Emp_tab ( Empno NUMBER(5) main KEY); alter TABLE Emp_tab include PRIMARY an essential (Empno); An alter TABLE statement that tries to enable an truth constraint will certainly fail if any type of rows that the table hurt the integrity constraint. The explain is rolled ago and the constraint meaning is not stored and also not enabled.

See Also:

\"Fixing Constraint Exceptions\" for much more information about rows that violate verity constraints.

developing Disabled border

The following produce TABLE and ALTER TABLE declaration both define and also disable verity constraints:

CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY vital DISABLE);ALTER TABLE Emp_tab include PRIMARY key (Empno) DISABLE; usage this an approach when creating tables that will certainly be loaded with huge amounts the data before anybody else accesses them, an especially if you must cleanse data after ~ loading it, or should fill in north columns with sequence number or parent/child relationships.

An change TABLE statement the defines and disables an truth constraints never fails, since its ascendancy is not enforced.

Enabling and Disabling present Integrity Constraints

Use the alter TABLE command to:

permitting Existing limit

Once you have actually finished cleansing data and filling in empty columns, friend can enable constraints the were disabled throughout data loading.

The adhering to statements are instances of explanation that allow disabled truth constraints:

ALTER TABLE Dept_tab allow CONSTRAINT Dname_ukey; change TABLE Dept_tab enable PRIMARY an essential permit UNIQUE (Dname) enable UNIQUE (Loc); An change TABLE statement the attempts to permit an truth constraint falls short when the rows the the table hurt the integrity constraint. The declare is rolled back and the constraint is not enabled.

See Also:

\"Fixing Constraint Exceptions\" for much more information around rows the violate integrity constraints.

Disabling present Constraints

If you need to perform a large load or update once the table currently contains data, you can temporarily disable limit to improve performance the the mass operation.

The following statements are examples of statements the disable allowed integrity constraints:

ALTER TABLE Dept_tab DISABLE CONSTRAINT Dname_ukey; alter TABLE Dept_tab DISABLE PRIMARY vital DISABLE distinctive (Dname) DISABLE unique (Loc); Tip: using the Data thesaurus to discover Constraints The preceding instances require that you understand constraint names and also which columns lock affect. To discover this information, you can query among the data dictionary views identified for constraints, USER_CONSTRAINTS or USER_CONS_COLUMNS. For much more information about these views, view \"Viewing interpretations of truth Constraints\" and medtox.org9i Database Reference.

Guidelines for permitting and Disabling key Integrity Constraints

When enabling or disabling UNIQUE, major KEY, and also FOREIGN vital integrity constraints, you need to be aware of several vital issues and prerequisites. UNIQUE crucial and PRIMARY key constraints room usually managed by the database administrator.

See Also:

\"Managing FOREIGN vital Integrity Constraints\" and also the medtox.org9i Database Administrator\"s Guide


Fixing Constraint Exceptions

When you try to create or enable a constraint, and also the declare fails due to the fact that integrity constraint exception exist, the statement is rolled back. You cannot enable the constraint till all exceptions are either to update or deleted. To determine which rows hurt the integrity constraint, encompass the EXCEPTIONS choice in the enable clause that a develop TABLE or transform TABLE statement.

See Also:

medtox.org9i Database Administrator\"s Guide for much more information around fixing constraint exceptions.

Altering integrity Constraints

Starting with medtox.org8i, girlfriend can transform the state that an present constraint with the change CONSTRAINT clause.

See Also:

For information on the parameters you can modify, check out the transform TABLE ar in medtox.org9i SQL Reference.

change CONSTRAINT example #1

The following commands show several options for whether the check constraint is enforced, and also when the constraint check is done:

CREATE TABLE X1_tab (a1 NUMBER CONSTRAINT y inspect (a1>3) DEFERRABLE DISABLE);ALTER TABLE X1_tab change CONSTRAINT Y_cnstrt ENABLE;ALTER TABLE X1_tab modify CONSTRAINT Y_cnstrt RELY;ALTER TABLE X1_tab modify CONSTRAINT Y_cnstrt at first DEFERRED;ALTER TABLE X1_tab change CONSTRAINT Y_cnstrt allow NOVALIDATE; change CONSTRAINT instance #2 The following regulates show several choices for whether the no NULL constraint is enforced, and also when the checking is done:

CREATE TABLE X1_tab (A1 NUMBER CONSTRAINT Y_cnstrt no NULL DEFERRABLE initially DEFERRED NORELY DISABLE);ALTER TABLE X1_tab add CONSTRAINT One_cnstrt UNIQUE(A1) DEFERRABLE INITIALLY prompt RELY making use of INDEX PCTFREE = 30ENABLE VALIDATE;ALTER TABLE X1_tab modify UNIQUE(A1)INITIALLY DEFERRED NORELY making use of INDEX PCTFREE = 40ENABLE NOVALIDATE;ALTER TABLE X1_tab modify CONSTRAINT One_cnstrtINITIALLY instant RELY; modify Constraint example #3 The following commands show several choices for even if it is the primary key constraint is enforced, and also when the checking is done:


Renaming integrity Constraints

because constraint names must be unique, even throughout multiple schemas, you can encounter problems when you want to clone a table and all that constraints, however the constraint surname for the new table disputes with the one for the initial table. Or, you can create a constraint with a default system-generated name, and later realize that it\"s better to provide the constraint a name the is basic to remember, so the you deserve to easily permit and disable it.

One the the properties you can transform for a constraint is its name. The complying with SQL*Plus script reflects you you can uncover the system-generated name for a constraint and adjust it come a surname of your choosing:

prompt go into table name to uncover its major key:accept table_nameselect constraint_name native user_constraints whereby table_name = upper(\"&table_name.\") and also constraint_type = \"P\";prompt Enter brand-new name because that its primary key:accept new_constraintset serveroutput ondeclare-- USER_CONSTRAINTS.CONSTRAINT_NAME is declared as VARCHAR2(30).-- utilizing %TYPE here protects us if the length alters in a future release. Constraint_name user_constraints.constraint_name%type;begin choose constraint_name into constraint_name native user_constraints where table_name = upper(\"&table_name.\") and constraint_type = \"P\"; dbms_output.put_line(\"The primary crucial for \" || upper(\"&table_name.\") || \" is: \" || constraint_name); execute instant \"alter table &table_name. Rename constraint \" || constraint_name || \" to &new_constraint.\";end;/

Dropping verity Constraints

autumn an integrity constraint if the preeminence that the enforces is no longer true or if the constraint is no much longer needed. Drop an integrity constraint making use of the transform TABLE command and also the fall clause. For example, the following statements autumn integrity constraints:

ALTER TABLE Dept_tab DROP distinct (Dname); alter TABLE Dept_tab DROP unique (Loc); change TABLE Emp_tab DROP major KEY, autumn CONSTRAINT Dept_fkey; fall TABLE Emp_tab CASCADE CONSTRAINTS; once dropping UNIQUE, major KEY, and FOREIGN vital integrity constraints, you have to be conscious of several important issues and prerequisites. UNIQUE and also PRIMARY crucial constraints space usually regulated by the database administrator.

See Also:

\"Managing FOREIGN crucial Integrity Constraints\" and also the medtox.org9i Database Administrator\"s Guide.

Managing FOREIGN vital Integrity Constraints

General information about defining, enabling, disabling, and dropping all types of integrity constraints is provided in the previous sections. The complying with section additional this information, concentrating specifically top top issues about FOREIGN an essential integrity constraints, i m sorry enforce relationships between columns in different tables.

Rules for FOREIGN vital Integrity Constraints

The following topics room of interest when defining FOREIGN an essential integrity constraints.

Datatypes and Names for Foreign crucial Columns

You should use the very same datatype for corresponding columns in the dependent and referenced tables. The shaft names execute not must match.

limit on Columns in Composite international Keys

Because international keys reference primary and unique secrets of the parental table, and PRIMARY vital and UNIQUE crucial constraints are imposed using indexes, composite international keys are minimal to 32 columns.

Foreign key References Primary vital by Default

If the obelisk list is not included in the REFERENCES choice when defining a FOREIGN crucial constraint (single obelisk or composite), then assumes the you intend to recommendation the primary vital of the stated table. Alternatively, girlfriend can explicitly specify the column(s) to recommendation in the parent table in ~ parentheses. immediately checks come verify the this obelisk list referrals a main or unique crucial of the parental table. If that does not, climate an many information error is returned.

Privileges forced to create FOREIGN an essential Integrity limit

To develop a FOREIGN vital constraint, the creator of the constraint must have actually privileged access to both the parent and the kid table.

In both cases, crucial privileges cannot be obtained through a role; they should be explicitly granted to the creator the the constraint.

These restrictions allow:

selecting How international Keys force Referential truth enables different varieties of referential integrity action to it is in enforced, as specified with the meaning of a FOREIGN vital constraint:

Restriction on enabling FOREIGN vital Integrity Constraints

FOREIGN key integrity constraints can not be permitted if the referenced primary or unique key\"s constraint is not existing or not enabled.

Viewing meanings of integrity Constraints

The data dictionary has the complying with views the relate come integrity constraints:

You can query this views to uncover the names of constraints, what columns they affect, and also other info to help you manage constraints.

See more: Son Of Crawmerax Walkthrough Victims Of The Vault Hunters, Borderlands 2: Find Sparky

Examples of specifying Integrity Constraints

Consider the following create TABLE explanation that specify a variety of integrity constraints:

CREATE TABLE Dept_tab ( Deptno NUMBER(3) primary KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT Dname_ukey distinct (Dname, Loc), CONSTRAINT LOC_CHECK1 examine (Loc IN (\"NEW YORK\", \"BOSTON\", \"CHICAGO\"))); produce TABLE Emp_tab ( Empno NUMBER(5) primary KEY, Ename VARCHAR2(15) no NULL, project VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT Mgr_fkey recommendations Emp_tab top top DELETE CASCADE, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) not NULL CONSTRAINT Dept_fkey referrals Dept_tab); example 1: Listing all of Your easily accessible Constraints The adhering to query lists every constraints characterized on all tables obtainable to the user:

SELECT Constraint_name, Constraint_type, Table_name, R_constraint_name from User_constraints; Considering the example statements at the beginning of this section, a list comparable to the one listed below is returned:

CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME --------------- - ----------- ------------------ SYS_C00275 ns DEPT_TAB DNAME_UKEY U DEPT_TAB LOC_CHECK1 C DEPT_TAB SYS_C00278 C EMP_TAB SYS_C00279 C EMP_TAB SYS_C00280 p EMP_TAB MGR_FKEY R EMP_TAB SYS_C00280 DEPT_FKEY R EMP_TAB SYS_C00275 an alert the following:


An extr constraint type is indicated by the character \"V\" in the CONSTRAINT_TYPE column. This constraint kind corresponds come constraints created by the WITH examine OPTION because that views. View Chapter2, \"Managing Schema Objects\" for much more information around views and the WITH check OPTION.

instance 2: distinguishing NOT NULL constraints from examine Constraints

In the vault example, several constraints are listed with a constraint type of \"C\". To distinguish which constraints space NOT NULL constraints and which are inspect constraints in the EMP_TAB and also DEPT_TAB tables, concern the adhering to query:

SELECT Constraint_name, Search_condition from User_constraints wherein (Table_name = \"DEPT_TAB\" OR Table_name = \"EMP_TAB\") and also Constraint_type = \"C\"; Considering the example create TABLE statements in ~ the beginning of this section, a list comparable to the one listed below is returned:

CONSTRAINT_NAME SEARCH_CONDITION--------------- ---------------------------------------- LOC_CHECK1 loc IN (\"NEW YORK\", \"BOSTON\", \"CHICAGO\") SYS_C00278 ENAME IS no NULL SYS_C00279 DEPTNO IS no NULL notice the following:

example 3: Listing tower Names the Constitute an verity Constraint

The complying with query lists every columns the constitute the constraints characterized on all tables easily accessible to you, the user:

SELECT Constraint_name, Table_name, Column_name indigenous User_cons_columns; Considering the example statements in ~ the beginning of this section, a list comparable to the one below is returned:

Book List
Master Index