PRIMARY KEY, UNIQUE
, and FOREIGN KEY
constraints are the most common types of constraints for a relational database. Occasionally, you may want to define your own type of constraint to enforce integrity control on the data in a table. The CHECK
constraint allows you to define all types of additional integrity rules for the data in a particular column. The CHECK
constraint is an expression that returns a value of either TRUE, FALSE,
or NULL
. If a SQL statement from a user causes a CHECK
constraint to return a value of FALSE, the Oracle database returns an error for that SQL statement. If a CHECK
constraint returns NULL
as a value, Oracle does not consider the constraint to be violated and does not return an error. You may have multiple CHECK
constraints on a column. There are some limitations on CHECK
constraints that make them different from other constraints:
- Any column values in the constraint must refer only to columns in the current row.
- Any column values in the constraint must exist in the same table as the constraint.
- Certain functions, such as
SYSDATE
orUSER
, and certain pseudo-columns, such asCURRVAL
orNEXTVAL
, cannot be used in theCHECK
constraint.
NULL Value
The NULL value is one of the key features of the relational database. The NULL, in fact, does not represent any value at al, instead it represents the lack of a value. When you create a column for a table that must have a value, you specify it as NOT NULL, meaning that it cannot contain a NULL value. If you try to write a row to a database table that does not assign a value to a NOT NULL column, Oracle will return an error.
You can assign NULL as a value for any datatype. The NULL value introduces what is called three-state logic to your SQL operators. A normal comparison has only two states: 1) TRUE or 2) FALSE. If you are making a comparison that involves a NULL value, there are three logical states: TRUE, FALSE, and neither. None of the following conditions are true for Column A if the column contains a NULL value:
You can assign NULL as a value for any datatype. The NULL value introduces what is called three-state logic to your SQL operators. A normal comparison has only two states: 1) TRUE or 2) FALSE. If you are making a comparison that involves a NULL value, there are three logical states: TRUE, FALSE, and neither. None of the following conditions are true for Column A if the column contains a NULL value:
A > 0 A < 0 A = 0 A != 0
The existence of three-state logic can be confusing for end users, but your data may frequently require you to allow for NULL values for columns or variables. You have to test for the presence of a NULL value with the relational operator IS NULL, since a NULL value is not equal to 0 or any other value. Even the expression:
will always evaluate to FALSE, since a NULL value does not equal any other value.
NULL = NULL
will always evaluate to FALSE, since a NULL value does not equal any other value.
Define Table Trigger
If you want to define a type of integrity logic that is outside of these restrictions, you can define a trigger.