Managing Constraints in Oracle
Constraints are rules that you can add to your table and column definitions.
These rules govern the data values that can be entered into a table or column.
This module discusses how to
- Identify the four basic types of constraints
PRIMARY KEY constraints on database tables
- Create unique constraints on database tables
- Create foreign key constraints on database tables
- Identify how
CHECK constraints work
- Use the
EXCEPTIONS INTO clause of the
- Identify how constraints are implemented
- Find information about constraints in the data dictionary
Adding constraints to the tables and columns in your database helps guarantee the quality of the data in the database. The lower the quality of the
data, the lower the quality of decisions that are made on the data. Since the primary purpose of data collection is to enable intelligent
decisionmaking, the proper use of constraints provides much of the power of your Oracle database.
Maintaining Referential Integrity in a Distributed System
If a part of a distributed statement fails, for example, due to an integrity constraint violation, the database returns error number ORA-02055. Subsequent statements or
procedure calls return error number ORA-02067 until a ROLLBACK or ROLLBACK TO SAVEPOINT is issued.
Design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should roll
back the entire transaction before allowing the application to proceed. The database does not permit declarative referential integrity constraints to be defined
across nodes of a distributed system. In other words, a declarative referential integrity constraint on one table cannot specify a foreign key that references a primary or
unique key of a remote table. Nevertheless, you can maintain parent/child table relationships across nodes using triggers.
If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the
parent table, but also the child table. For example, assume that the child table is in the sales database and the parent table is in the hq database. If the network connection
between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table)
cannot proceed because the referential integrity triggers must have access to the parent table in the hq database.