<< Creating a Table with SQL Script | Adding Integrity Constraints >>

Ensuring Data Integrity

Creating and Using Tables

3-6 Oracle Database 2 Day Developer's Guide

The results of the statement follow.

CREATE TABLE succeed.

You created a new table,

scores

. If you click the table, the table will appear on the

right side of the SQL Developer window, showing its new columns. If you click the
SQL tab, you will see the script that created this table. You may need to click the
Refresh icon.

Ensuring Data Integrity

The data in the table must satisfy the business rules that are modeled in the
application. Many of these rules can be implemented through integrity constraints
that use the SQL language to explicitly state what type of data values are valid for each
column.

When an integrity constraint applies to a table, all data in the table must conform to
the corresponding rule, so when your application includes a SQL statement that
inserts or modifies data in the table, Oracle Database automatically ensures that the
constraint is satisfied. If you attempt to insert, update, or remove a row that violates a
constraint, the system generates an error, and the statement is rolled back. If you
attempt to apply a new constraint to a populated table, the system may generate an
error if any existing row violates the new constraint.

Because Oracle Database checks that all the data in a table obeys an integrity
constraint much faster than an application can, you can enforce the business rules
defined by integrity constraints more reliably than by including this type of checking
in your application logic.

Understanding Types of Data Integrity Constraints

There are five basic types of integrity constraints:

A NOT NULL constraint ensures that the column contains data (it is not null).

A unique constraint ensures that multiple rows do not have the same value in the
same column. This type of constraint can also be used on combination of columns,
as a composite unique constraint. This constraint ignores null values.

A primary key constraint combines

NOT NULL

and

UNIQUE

constraints in a single

declaration; it prevents multiple rows from having the same value in the same
column or combination of columns, and prevents null values.

A foreign key constraint requires that for each value in the column on which the
constraint is defined, there must be a matching value in a specified other table and
column.

A check constraint ensures that a value satisfies a specified condition. Use check
constraints when you need to enforce integrity rules based on logical expressions,
such as comparisons. Oracle recommends that you never use check constraints
when other types of constraints can provide the necessary checking.

See Also:

Oracle Database SQL Language Reference for information on the

CREATE TABLE

statement

See Also:

Oracle Database SQL Language Reference for information about
integrity constraints