NOT NULL Constraints
The NOT NULL constraint ensures that the column contains no null values.
Columns without the NOT NULL constraint can contain null values by default.
NOT NULL constraints must be defined at the column level.
A NOT NULL constraint prohibits a column from containing nulls. The NULL keyword by itself does not actually define an integrity constraint, but you can specify it to explicitly permit a column to contain nulls. You must define NOT NULL and NULL using inline specification.
If you specify neither NOT NULL nor NULL, then the default is NULL.
NOT NULL constraints are the only constraints you can specify inline on XMLType and VARRAY columns.
To satisfy a NOT NULL constraint, every row in the table must contain a value for the column.
Restrictions on NOT NULL Constraints
NOT NULL constraints are subject to the following restrictions:
• You cannot specify NULL or NOT NULL in a view constraint.
• You cannot specify NULL or NOT NULL for an attribute of an object. Instead, use a CHECK constraint with the IS [NOT] NULL condition.
For example,
The hr.employees table requires a value in the email column. An attempt to insert an employee row without an email address generates an error:
SQL> INSERT INTO hr.employees (employee_id, last_name) values (999,'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")
Unique Constraints
A unique constraint designates a column as a unique key.
A UNIQUE key integrity constraint requires that every value in a column or a set of columns (key) be unique—that is, no two rows of a table can have duplicate values in a specified column or a set of columns.
The column (or set of columns) included in the definition of the UNIQUE key constraint is called the unique key.
If the UNIQUE constraint comprises more than one column, that group of columns is called a composite unique key.
UNIQUE constraints enable the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without the NOT NULL constraints because nulls are not considered equal to anything.
A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE constraint
A composite unique key designates a combination of columns as the unique key. When you define a unique constraint inline, you need only the UNIQUE keyword. When you define a unique constraint out of line, you must also specify one or more columns. You must define a composite unique key out of line.
To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls.
To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint.
However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.
When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement.
You can also use the CREATE UNIQUE INDEX statement to create a unique function-based index that defines a conditional unique constraint.
Restrictions on Unique Constraints
Unique constraints are subject to the following restrictions:
• None of the columns in the unique key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.
• A composite unique key cannot have more than 32 columns.
• You cannot designate the same column or combination of columns as both a primary key and a unique key.
• You cannot specify a unique key when creating a subview in an inheritance hierarchy.
The unique key can be specified only for the top-level (root) view.
• When you specify a unique constraint for an external table, you must specify the RELY and DISABLE constraint states. See External Table Constraints for more information.
Defined at either the table level or the column level:
The emp_email_uk constraint ensures that no two employees have the same email address, as shown in the following example:
SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';
EMPLOYEE_ID LAST_NAME EMAIL
----------- ------------------------- -------------------------
202 Fay PFAY
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
1 VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULL constraints are typical. This combination forces the user to enter values in the unique key and
eliminates the possibility that new row data conflicts with existing row data.
SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';
EMPLOYEE_ID LAST_NAME EMAIL
----------- ------------------------- -------------------------
202 Fay PFAY
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
1 VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
