#REFERENTIAL INTEGRITY HOW TO#

Some employees might not have a commission. Only define NOT NULL constraints for columns of a table that absolutely require values at all times.įor example, a new employee's manager or hire date might be temporarily omitted. When to Use NOT NULL Integrity Constraintsīy default, all columns can contain nulls. You should almost always index foreign keys, and the database does not do this for you. If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint.The column orders in the index and the constraint do not need to match.

At most one unique or primary key can use each non-unique index.

They can even use just the first few columns of non-unique indexes.

Unique and primary keys can use non-unique as well as unique indexes.

Constraints use existing indexes where possible, rather than creating new ones.

You should create these indexes by hand, rather than letting the database create them for you. Creating Indexes for Use with ConstraintsĪll enabled unique and primary keys require corresponding indexes. This technique is appropriate when you can determine that data values are wrong or out of range, without checking against any data already in the table. This might let you provide instant feedback to the user, and reduce the load on the database. You might enforce business rules through application logic as well as through integrity constraints, if you can filter out bad data before attempting an insert or update. When to Enforce Business Rules in Applications But this method is less reliable than the integrity constrain, because SELECT in Oracle uses 'consistent read' and so the query might miss uncommitted changes from other transactions. To enforce this rule without integrity constraints, you can use a trigger to query the department table and test that each new employee's department is valid. When you add a new employee record to the table, Oracle automatically checks that its department number appears in the department table. Then, create a rule that every department listed in the employee table must match one of the values in the department table: ALTER TABLE Emp_tabĪDD FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno) To ensure that each employee works for a valid department, first create a rule that all values in the department table are unique : ALTER TABLE Dept_tab Example of an Integrity Constraint for a Business Rule Oracle can check that all the data in a table obeys an integrity constraint faster than an application can. You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. When to Enforce Business Rules with Integrity Constraints When you issue a SQL statement that modifies data in the table, Oracle ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program. When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. Because each company defines its own policies about things like salaries, employee numbers, inventory tracking, and so on, you can specify a different set of rules for each database table. Business rules specify conditions and relationships that must always be true, or must always be false. You can define integrity constraints to enforce business rules on data in your tables.

Viewing Definitions of Integrity Constraints.Managing FOREIGN KEY Integrity Constraints.Enabling and Disabling Integrity Constraints.Examples of Defining Integrity Constraints.When to Use CHECK Integrity Constraints.About Referential Integrity in a Distributed Database.Managing Constraints That Have Associated Indexes.Enforcing Referential Integrity with Constraints.

#REFERENTIAL INTEGRITY HOW TO#

This chapter explains how to enforce the business rules associated with your database and prevent the entry of invalid information into tables by using integrity constraints. Maintaining Data Integrity Through Constraints

Referential integrity