|Lesson 6||CHECK constraints|
|Objective||Identify how CHECK constraints work. |
Add not null Constraint to existing Table
Identify how CHECK constraints work in Oracle
PRIMARY KEY, UNIQUE
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
constraint allows you to define all types of additional integrity rules for the data in a particular column. The
constraint is an expression that returns a value of either
. If a SQL statement from a user causes a
constraint to return a value of FALSE, the Oracle database returns an error for that SQL statement. If a
as a value, Oracle does not consider the constraint to be violated and does not return an error. You may have multiple
constraints on a column. There are some limitations on
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
USER, and certain pseudo-columns, such as
NEXTVAL, cannot be used in the
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:
A > 0
A < 0
A = 0
A != 0
Define Table Trigger
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:
NULL = NULL
will always evaluate to FALSE, since a NULL value does not equal any other value.
If you want to define a type of integrity logic that is outside of these restrictions, you can define a trigger.
There are no
CHECK constraints required in the
COIN database, but if you want to ensure that all auctions stopped after they began, you can define a
CHECK constraint. View the code below to see how.
CHECK Constraints Example
CREATE TABLE AUCTION (auction_id NUMBER,
CONSTRAINT check_times CHECK (stop_time > start_time));
NOT NULL Constraint
Oracle also supports another type of standard constraint, the
NOT NULL constraint. However, Oracle treats the
NOT NULL constraint as a type of
CHECK constraint, because the
limitation returns a Boolean result.
The paragraph below discusses information relating the constraint type to its function when managing database objects in Oracle.
Oracle Constraint Types
|Requires a value on the constrained column|
NULL values, or an exclusive value if not
|Cannot reference values in another table or other rows in its own table|
|Only one allowed per table|
|Requires the existence of a primary key on the referenced table
Managing Integrity Constraints
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.
An integrity constraint is a named rule that restrict the values for one or more columns in a table. These rules prevent invalid data entry into tables. Also, constraints can prevent the deletion of a table when certain dependencies exist. If a constraint is enabled, then the database checks data as it is entered or updated. Oracle Database prevents data that does not conform to the constraint from being entered. If a constraint is disabled, then Oracle Database allows data that does not conform to the constraint to enter the database.
In the following example, the CREATE TABLE statement specifies NOT NULL constraints for the last_name, email, hire_date, and job_id columns.
The constraint clauses identify the columns and the conditions of the constraint. These constraints ensure that the specified columns contain no null values. For example, an attempt to insert a new employee without a job ID generates an error.
You can create a constraint when or after you create a table. You can temporarily disable constraints if needed. The database stores
constraints in the data dictionary.
Example 4-6 CREATE TABLE employees
The following example shows the CREATE TABLE statement for the employees table in the hr sample schema. The statement specifies columns such as employee_id, first_name, and so on, specifying a data type such as NUMBER or DATE for each column.
CREATE TABLE employees
(employee_id NUMBER(6),first_name VARCHAR2(20),
CONSTRAINT emp_last_name_nn NOT NULL,
email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL,
phone_number VARCHAR2(20), hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL,
job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL,
salary NUMBER(8,2), commission_pct NUMBER(2,2),
manager_id NUMBER(6), department_id NUMBER(4),
CHECK (salary > 0), CONSTRAINT emp_email_uk
UNIQUE (email)) ;
Oracle Database Admin 18c
The next lesson explores adding constraints to tables that already hold data.