Oracle Concepts - 5 Data Integrity

Data Integrity

- integrity constraints enforce the business rules  is a schema object that is created and dropped using SQL, associated with a database and prevent the entry of invalid information into tables, must always be true or must always be false

- Techniques for Guaranteeing Data Integrity

• Enforcing business rules with triggered stored database procedures
• Using stored procedures to completely control access to data
• Enforcing business rules in the code of a database application
• Using Oracle Database integrity constraints, which are rules defined at the column or object level that restrict values in the database

- Advantages of integrity constraints over alternatives for enforcing data integrity include:

1. Declarative ease

• no additional programming is required when you define or alter a table
• SQL statements are easy to write and eliminate programming errors.

2. Centralized rules

• defined for tables and are stored in the data dictionary
• all applications must adhere to the same integrity constraints, applications need not change
• applications can use metadata in the data dictionary to immediately inform users of violations

3. Flexibility when loading data

• disable & enable integrity constraints temporarily to avoid performance overhead when loading large amounts of data

- Constraint specified as

1. part of the definition of a column or attribute is called an inline2. part of the table definition is called an out-of-line specification
3. create after table creation using alter table called after-creation

- key is the column or set of columns included in the definition of certain types of integrity constraints

- Types of Constraints

1. NOT NULL, Allows or disallows inserts or updates of rows containing a null in a specified column
2. Unique key, Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
3. Primary key, It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

A primary key can be natural or a surrogate. 

1. natural key is a meaningful identifier made of existing attributes in a table. For example, a natural key could be a postal code in a lookup table.
2. surrogate key is a system-generated incrementing identifier that ensures uniqueness within a table. Typically, surrogate keys are generated by a sequence.

- implementation of the primary key constraint guarantees
a. No duplicate values
b. Not allow nulls
c. creates a unique index and a NOT NULL constraint

d. when you create a primary key with a Deferrable Constraint , the generated index is not unique

4. Foreign key, Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key. Oracle Database can enforce the relationship between the two tables through a foreign key constraint, also called referential integrity constraint.

- Referential Integrity Constraint Terms

• Foreign key > department_id column in employees is a foreign key
• Referenced key > department_id column in departments is the referenced key
• Dependent or child table > employees table is a child of departments
• Referenced or parent table > departments table is a parent of employees

5. Check, Requires a database value to obey a specified condition.

6. REF, Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values