Oracle Constraint Types[table summary]

Oracle Constraint Types

Types of constraints

`NOT NULL`	Requires a value on the constrained column
`UNIQUE`	Allows `NULL` values, or an exclusive value if not `NULL`
`CHECK`	Cannot reference values in another table or other rows in its own table
`PRIMARY KEY`	Only one allowed per table
`FOREIGN KEY`	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.

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.
Oracle Database Admin 18c

CREATE TABLE employees
(employee_id NUMBER(6),first_name VARCHAR2(20), 
last_name VARCHAR2(25) 
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), 
CONSTRAINT emp_salary_min
CHECK (salary > 0), CONSTRAINT emp_email_uk
UNIQUE (email)) ;