Which two statements are true regarding constraints?

Justyna

You can use statement like:
alter table
table_name
DISABLE constraint
constraint_name;

From documentation:
“If a constraint is disabled, then data that does not conform can be allowed to enter the database.”

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#i1006646

Ritam Tiwari

Integrity Constraint StatesYou 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:

•ENABLE, VALIDATE

•ENABLE, NOVALIDATE

•DISABLE, VALIDATE

•DISABLE, NOVALIDATE

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 ConstraintsTo 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:

1.Disable state.

2.Perform the operation (load, export, import).

3.Enable novalidate state.

4.Enable state.

Some benefits of using constraints in this order are:

•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:

CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY DISABLE, . . . ;

ALTER TABLE emp
ADD PRIMARY KEY (empno) DISABLE;
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:

CREATE TABLE emp (
empno NUMBER(5) CONSTRAINT emp.pk PRIMARY KEY, . . . ;

ALTER TABLE emp
ADD CONSTRAINT emp.pk PRIMARY KEY (empno);
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.

Note:

An efficient procedure for enabling a constraint that can make use of parallelism is described in “Efficient Use of Integrity Constraints: A Procedure”.
See Also:

“Creating an Index Associated with a Constraint”
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.

ALTER TABLE dept
DISABLE CONSTRAINT dname_ukey;

ALTER TABLE dept
DISABLE PRIMARY KEY KEEP INDEX,
DISABLE UNIQUE (dname, loc) KEEP INDEX;
The following statements enable novalidate disabled integrity constraints:

ALTER TABLE dept
ENABLE NOVALIDATE CONSTRAINT dname_ukey;

ALTER TABLE dept
ENABLE NOVALIDATE PRIMARY KEY,
ENABLE NOVALIDATE UNIQUE (dname, loc);
The following statements enable or validate disabled integrity constraints:

ALTER TABLE dept
MODIFY CONSTRAINT dname_key VALIDATE;

ALTER TABLE dept
MODIFY PRIMARY KEY ENABLE NOVALIDATE;
The following statements enable disabled integrity constraints:

ALTER TABLE dept
ENABLE CONSTRAINT dname_ukey;

ALTER TABLE dept
ENABLE PRIMARY KEY,
ENABLE UNIQUE (dname, loc);
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:

ALTER TABLE dept
DISABLE PRIMARY KEY CASCADE;
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:

ALTER TABLE dept
RENAME CONSTRAINT dname_ukey TO dname_unikey;
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

•DROP UNIQUE

•DROP CONSTRAINT

The following two statements drop integrity constraints. The second statement keeps the index associated with the PRIMARY KEY constraint:

ALTER TABLE dept
DROP UNIQUE (dname, loc);

ALTER TABLE emp
DROP PRIMARY KEY KEEP INDEX,
DROP CONSTRAINT dept_fkey;
If FOREIGN KEYs reference a UNIQUE or PRIMARY KEY, you must include the CASCADE CONSTRAINTS clause in the DROP statement, or you cannot drop the constraint.

Ritam Tiwari

Column constraints include:
NOT NULL
Specifies that this column cannot hold NULL values (constraints of this type are not nameable).

PRIMARY KEY
Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.

Note: If you attempt to add a primary key using ALTER TABLE and any of the columns included in the primary key contain null values, an error will be generated and the primary key will not be added. See ALTER TABLE statement for more information.
UNIQUE
Specifies that values in the column must be unique.

FOREIGN KEY
Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL.

CHECK
Specifies rules for values in the column.

Table constraints include:
PRIMARY KEY
Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.

UNIQUE
Specifies that values in the columns must be unique.

FOREIGN KEY
Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.

Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
CHECK
Specifies a wide range of rules for values in the table.

Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column.