Oracle Database Constraints

Oracle DatabaseIt is often used in our daily work. This article will bring you an Oracle databaseConstraintsTo help you.

The last table has two columns of fields, which either have values or are empty. A simple table definition cannot meet this requirement and must use the constraint constraints of Oracle. Constraints are mainly used to ensure data integrity.

From the TOAD settings, we can easily see that there are four types of constraints: Primary Key), Check), Unique), Foreign Key ). The other two types are not null and REF. REF is the value of one or more columns in another table.

The following is a detailed introduction to table 6.

Not null constraint prohibits a database value from being null.

Unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

Primary Key constraint combines a not null constraint and a unique constraint in a single declaration. it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

Foreign Key constraint requires values in one table to match values in another table.

Check constraint requires a value in the database to comply with a specified condition.

REF column by definition references an object in another object type or in a relational table. a ref constraint lets you further describe the relationship between the REF column and the object it references.

For our requirements, Check is met. You can add A condition that is (A is null and B is null) or (A is not null and B is not null).

There are two main constraints: Status on Creation, that is, whether to use constraints when adding or modifying data in the table. The optional values are Enabled and Disabled; the other is Validation, which indicates whether to verify the existing data in the table. The optional values are Validate and NoValidate.

There are four combinations of the above two States. The following is a detailed description of the four States.

Enable validate is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

Enable novalidate means that the constraint is checked, but it does not have to be true for all rows. this allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

In an alter table statement, enable novalidate resumes constraint checking on disabled constraints without first validating all data in the table.

Disable novalidate is the same as DISABLE. The constraint is not checked and is not necessarily true.

Disable validate disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.

The constraint setting also has a delay setting. The default setting is non-delay, that is, Initially Immediate. In this case, any modifications will be verified, and the other one will be delayed, that is, Intially Deferred, it will be verified when all modifications are completed and then rolled back.