Lesson 10
Managing Oracle Constraints Conclusion
One of the key qualities of data in your Oracle database is its integrity. This module discussed how constraints can help guarantee the integrity of the data in your database.
In this module, you learned to:
- Identify the four basic types of constraints
- Create
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 CONSTRAINT
definition.
- Identify how constraints are implemented
- Find information about constraints in the data dictionary
Managing Constraints Glossary
In this module, you learned the following glossary term:
- Referential Integrity: The guarantee that a data value in one column refers to an already existing value in another column.
The next module is about indexes.
create table cust_order
(order_number number(6) primary key,
order_date date not null,
delivery_date date,
warehouse_number number default 12,
customer_number number not null,
order_line_item_qty number check (order_line_item_qty < 100),
ups_tracking_number varchar2(50) unique,
foreign key (customer_number) references customer(customer_number));
Referential Integrity Values
The referential integrity or FOREIGN KEY constraint is more complicated than the others we have covered so far because it relies on another table to restrict what values can be entered into the column with the referential integrity constraint. In the preceding example, a FOREIGN KEY is declared on the CUSTOMER_NUMBER column; any values entered into this column must also exist in the CUSTOMER_NUMBER column of another table (in this case, the CUSTOMER table). As with other constraints that allow NULL values, a column with a referential integrity constraint can be NULL without requiring that the referenced column contain a NULL value.
Furthermore, a FOREIGN KEY constraint can be self-referential. In an EMPLOYEE table whose primary key is EMPLOYEE_NUMBER, the MANAGER_NUMBER column can have a FOREIGN KEY declared against the EMPLOYEE_NUMBER column in the same table.
This allows for the creation of a reporting hierarchy within the EMPLOYEE table itself. Indexes should almost always be declared on a FOREIGN KEY column to improve performance; the only exception to this rule is when the referenced primary or unique key in the parent table is never updated or deleted.
Modifying Constraints - Quiz