Databases Reference
In-Depth Information
Constraints
Constraints
are a way to validate the data in a column or columns of a table.
The Oracle database has five distinct types of constraints that can be defined on
a column or columns in a table:
constraint
A condition defined against a column or
columns on a table in the database to
enforce business rules or relationships
between tables in the database.
NOT
NULL
,
CHECK
,
UNIQUE
,
PRIMARY
KEY
, and
FOREIGN
constraint, as its name implies, does its
validation in reference to another table within the database.
KEY
. Only the
FOREIGN
KEY
The end-user application frequently validates the data entered into the database,
even before an
operation occurs, and this might be the best way
to implement complex business rules. The ways in which business rules are imple-
mented in applications can be varied and complex. For more information about data
validation through the use of business rules in applications, see the topic
INSERT
or
UPDATE
Business
Rules Applied: Building Better Systems Using the Business Rules Approach
by Bar-
bara Von Halle. Oracle separates the business rules enforcement from both the client
and the server with its Business Components for Java (BC4J) product. More informa-
tion on BC4J can be found at
http://otn.oracle.com/products/jdev/htdocs/
bc4j9irc_datasheet.html
. Oracle's Oracle Technology Network (OTN) is a free
website but requires you to register with a valid e-mail address.
Constraints, like many other database objects, can be defined when the table
is defined or added to the table later. You can also remove, disable, or enable
existing constraints.
Any constraint can have a name assigned to it when it is created. If you do not
explicitly assign a name, Oracle will give the constraint a system-assigned name.
The
constraint can be defined only at the column level. All other con-
straints can be defined at the column level or at the table level. Some constraints,
such as a constraint that compares the values of two columns, must necessarily
be defined at the table level.
NULL
NOT NULL
The
is the most straightforward of all the constraints. It
specifies that a column will not allow
NOT NULL constraint
NOT NULL constraint
A constraint that prevents
values
from being entered into a column of a table.
NULL
NULL
values, regardless of its datatype. The
syntax for a
NOT
NULL
constraint is as follows:
[CONSTRAINT
<constraint name>
] [NOT] NULL
contains the job identifier, the
job description, and the minimum and maximum salary for the job. The table
structure is shown here with a
In Scott's widget database, the HR table
JOBS
DESCRIBE
command:
desc jobs
Name Null? Type
