Data Integrity

 

Introduction to Data Integrity

It is important that data maintain data integrity, which is adherence to business rules determined by the database administrator or application developer.

Business rules specify conditions and relationships that must always be true or must always be false. For example, each company defines its own policies about salaries, employee numbers, inventory tracking, and so on.

Techniques for Guaranteeing Data Integrity

When designing a database application, developers have several options for guaranteeing the integrity of data stored in the database.

These options include:

• 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

An integrity constraint is a schema object that is created and dropped using SQL. To enforce data integrity, use integrity constraints whenever possible.

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

• Declarative ease

Because you define integrity constraints using SQL statements, no additional programming is required when you define or alter a table. The SQL statements are easy to write and eliminate programming errors.

• Centralized rules

Integrity constraints are defined for tables and are stored in the data dictionary Thus, data entered by all applications must adhere to the same integrity constraints. If the rules change at the table level, then applications need not change. Also, applications can use metadata in the data dictionary to immediately inform users of violations, even before the database checks the SQL statement.

• Flexibility when loading data

You can disable integrity constraints temporarily to avoid performance overhead when loading large amounts of data. When the data load is complete, you can re-enable the integrity constraints.

Types of Integrity Constraints

NOT NULL

A NOT NULL constraint requires that a column of a table contain no null values. A null is the absence of a value. By default, all columns in a table allow nulls.

NOT NULL constraints are intended for columns that must not lack values.

 

For example, if you have a table of employees and the "birthdate" column is defined as "not null," it means that every employee record must have a birthdate specified; you cannot leave it blank.

 

In programming languages and SQL, you might encounter syntax like:

CREATE TABLE employees (

  employee_id INT PRIMARY KEY,

  first_name VARCHAR(50) NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  birthdate DATE NOT NULL

);

In this example, both the first_name and last_name columns must have values, and the birthdate column must also have a non-null date value.

The use of "not null" constraints helps ensure data integrity and makes it clear that certain information is required for each record.

Unique Constraints

A unique key constraint requires that every value in a column or set of columns be unique. No rows of a table may have duplicate values in a single column (the unique key) or set of columns (the composite unique key) with a unique key constraint.

 

For example, let's say you have a table of employees, and you want to make sure that each employee has a unique employee ID. You can apply a unique constraint to the "employee_id" column to enforce this rule. Here's an example using SQL:

 

CREATE TABLE employees (

  employee_id INT PRIMARY KEY,

  first_name VARCHAR(50),

  last_name VARCHAR(50),

  UNIQUE (employee_id)

);

In this case, the UNIQUE (employee_id) constraint ensures that each value in the "employee_id" column is unique across all rows in the "employees" table. Attempting to insert a new record with an employee ID that already exists in the table would result in a constraint violation error.

Unique constraints are valuable for maintaining data integrity and preventing duplicate entries in critical columns. They are commonly used for columns such as usernames, email addresses, or any other data where uniqueness is a requirement.

 

Primary Key Constraints

In a primary key constraint, the values in the group of one or more columns subject to the constraint uniquely identify the row. Each table can have one primary key, which in effect names the row and ensures that no duplicate rows exist.

A primary key can be natural or a surrogate. A 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. In contrast, a surrogate key is a system-generated incrementing identifier that ensures uniqueness within a table. Typically, a sequence generates surrogate keys.

The Oracle Database implementation of the primary key constraint guarantees that the following statements are true:

• No two rows have duplicate values in the specified column or set of columns.
• The primary key columns do not allow nulls.

For example using SQL:

CREATE TABLE students (

  student_id INT PRIMARY KEY,

  first_name VARCHAR(50),

  last_name VARCHAR(50),

  age INT

);

In this example, the student_id column is specified as the primary key for the "students" table. It means that each student in the table must have a unique student_id, and this column cannot contain null values.

 

The primary key is crucial for database relationships because it provides a way to uniquely identify each record in a table. It is often used as a reference in other tables (as a foreign key) to establish relationships between tables.

 

In addition to enforcing uniqueness and non-null values, the database management system typically uses the primary key to create an index for faster data retrieval and search operations.

 

Foreign Key Constraints

Whenever two tables contain one or more common columns, Oracle Database can enforce the relationship between the two tables through a foreign key constraint, also called a referential integrity constraint.

A foreign key constraint requires that for each value in the column on which the constraint is defined, the value in the other specified other table and column must match. An example of a referential integrity rule is an employee can work for only an existing department.

The following table lists terms associated with referential integrity constraints.

 

For example using SQL:

 

CREATE TABLE departments (

  department_id INT PRIMARY KEY,

  department_name VARCHAR(50)

);

 

CREATE TABLE employees (

  employee_id INT PRIMARY KEY,

  first_name VARCHAR(50),

  last_name VARCHAR(50),

  department_id INT,

  FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

In this example, the departments table has a primary key (department_id), and the employees table has a foreign key (department_id) that references the primary key in the departments table. This relationship indicates that the department_id in the employees table must correspond to a valid department_id in the departments table.

Key points about foreign keys:

Referential Integrity: Foreign keys ensure that relationships between tables remain consistent. They help maintain referential integrity, meaning that values in the foreign key column of one table correspond to values in the primary key column of another table.

CASCADE Options: When defining a foreign key, you can specify what actions should happen if the referenced row in the parent table is updated or deleted. For example, you can use the ON DELETE CASCADE option to automatically delete related rows in the child table when the corresponding row in the parent table is deleted.

Multiple Foreign Keys: A table can have multiple foreign keys, each referencing a different table. This is common in complex database designs where multiple relationships exist between tables.

Foreign keys play a crucial role in maintaining data integrity and establishing connections between different entities in a relational database.