8. Oracle Data Integrity

Data Integrity

Constraints, indexes, permissions, and roles

1. Data Integrity:

Data integrity is used to ensure that database data complies with certain commercial and logical rules. In Oracle, data integrity can be achieved using constraints, triggers, applications (processes and functions), because constraints are easy to maintain and have the best performance, therefore, it is the first choice to maintain data integrity.

 

1. Constraints

Constraints are used to ensure that database data meets special commercial rules. In Oracle, constraints include:

Not NULL: Not empty

If not null is defined in a column, data must be provided to the column during data insertion; otherwise, data cannot be inserted.

Unique: unique

When a unique constraint is defined, the column value cannot be repeated, but it can be null.

Primary Key: Primary Key

Used to uniquely identify the data of the table row. When the primary key constraint is defined, this column cannot be repeated and cannot be null.

Note: A table can have only one primary key, but multiple unique constraints are allowed.

Joint primary key: multiple columns are joined together as the primary key.

Foreign key: foreign key

Defines the relationship between a master table and a slave table. Foreign key constraints must be defined on the slave table. The master table must have a primary key constraint or a unique constraint. After defining foreign key constraints, the foreign key column data must exist in the primary key column of the master table or be null.

Check: Check

It is used to force the conditions that the row data must meet. It is assumed that the check constraint is defined on the Sal column and the Sal column value must be between and. If it is not in this range, an error is prompted.

 

1. Case Analysis

-- Create a commodity table Create Table goods ( Goodsid char (8) primary key, -- primary key Goodsname varchar2 (30 ), Unitprice number () Check (unitprice> 0), -- the unit price is greater than 0 CATEGORY varchar2 (8 ), Provider varchar2 (30 ));   -- Create a customer table Create Table customer ( Customerid char (8) primary key, -- primary key Name varchar2 (50) not null, Address varchar2 (50 ), Email varchar (50) Unique -- email cannot be repeated Sex char (2) default 'male' check (sex in ('male', 'female ')), Cardid char (18 ));   -- Purchase Create Table purchase ( Customerid char (8) Reference customer (customerid), -- foreign key Goodsid char (8) Reference goods (goodsid), -- foreign key Nums number (100) Check (Nums between 1 and 30)-quantity );

 

If you forget to create necessary constraints when creating a table, you can use the alter table command to add constraints to the table after creating the table. Note: When adding the not null constraint, you need to use the modify option, and add the other four constraints to use the Add option.

-- The product name cannot be blank.

Alter table goods modify goodsname not null;

-- The ID card cannot be added.

Alter table customer add constraint cardunique unique cardid;

-- The customer's address can only be "Haidian", "Chaoyang", "Dongcheng", "Xicheng", "Tongzhou", and "Chongwen"

Alter table customer add constraint addresschek check (address in (''haidian, 'chaoyang ', 'dongcheng', 'xicheng ', 'tongzhou', 'chongwen '));

 

1. Delete Constraints

Alter table indicates the name of the drop constraint.

Note:

An error may occur when deleting the primary key constraint, for example:

Alter table xxx drop primary key;

This is because if there is a master-slave relationship between the two tables, the cascade option must be included when you delete the primary key constraint of the primary table, for example:

Alter table xxx drop primary key cascade;

 

1. Show Constraints

1) display Constraints You can query the data dictionary view user_constraints to display all constraints of the current user. Select constraint_name, constraint_type, status, validated from user_constraint where table_name = 'table name '; 2) display constraint Columns You can query the data dictionary view user_cons_columns to display the column information of the table corresponding to the constraint. Select column_name, position from user_cons_columns where constraint_name = 'constraint name '; 3) You can use PL/SQL developer to view the solution.

 

1. Column-Level Definition

Column-level definition refers to defining constraints while defining columns.

For example, define primary key constraints in the department table.

Create Table Department (

Deptid number (4) Constraint pk_department primary key, -- primary key

Name varchar2 (8 ),

Loc varchar2 (20)

);

Note: When defining columns, you can add a constraint pk_department without a constraint. If the difference is not added, the system automatically assigns a primary key constraint name, which is less readable.

1. Table-Level Definition

Table-level definition refers to defining constraints after defining all columns. Note: not null can only be defined at the column level.

Take the primary key constraint and foreign key constraint defined when creating the employee2 table as an example:

Create Table employee2 (

Empid number (4 ),

Name varchar2 (20 ),

Loc varchar2 (40 ),

Constraint pk_employee primary key (empid ),

Constraint fk_department foreign key (deptid) References Department (deptid)

);

 

Index

1. Index Definition

An index is a data object used to accelerate data access. The rational use of indexes can greatly reduce the number of I/O operations, thus improving data access performance.

There are many indexes and several are commonly used.

1) Single Column Index

A single column index is an index created based on a single column, for example:

Create Index name on table name (field name );

2) Composite Index

A composite index is an index based on two or more columns. You can have multiple indexes on the same table, but the combination of columns must be different. For example:

Create index emp_idx1 on EMP (ename, job );

Create index emp_idx2 on EMP (job, name );

 

1. Indexing principles

1) creating an index on a large table makes sense.

2) create an index on the column frequently referenced in the WHERE clause or connection condition.

3) The index level should not exceed 4 layers.

 

1. Index disadvantages

Indexing has some inherent limitations:

1) To create an index, the system takes about 1.2 times the hard disk and memory space of the table to store the index;

2) When updating data, the system must have additional time to update the index at the same time to maintain data and index consistency.

Practices show that improper indexes not only do not help, but also reduce system performance. Because a large number of indexes take more time to insert, modify, and delete than no index.

For example, it is inappropriate to create an index in the following fields:

1) Few or never reference fields

2) logic fields, such as men and women, and whether or not.

In summary, improving query efficiency is at the cost of consuming a certain amount of system resources. indexes cannot be established blindly, which is an important indicator to test whether DBAs are excellent.

1. Other Indexes

Data storage can be divided into B * trees, reverse indexes, and bitmap indexes;

It can be classified by the number of index columns into single-column indexes and composite indexes;

Based on the uniqueness of the index column value, it can be divided into unique and non-unique indexes;

In addition, there are also function indexes, global indexes, and partition indexes.

We also need to say that we will create indexes on different columns or even different types of indexes in different situations. Remember that technology is dead and people are active.

For example, B * tree indexes are built on columns with few duplicate values, while Bitmap indexes are built on columns with many duplicate values and different values are relatively fixed.

 

1. Display index information

1) display all indexes of the table

You can have multiple indexes on the same table. You can query the index information in the data dictionary view dba_indexs and user_indexs. dba_indexs is used to display all the index information of the database, user_indexs is used to display the index information of the current user.

Select index_name, index_type from user_indexs where table_name = 'table name ';

2) display index Columns

You can query the data dictionary view user_ind_columns to display the information of the columns corresponding to the index.

Select table_name, column_name from user_ind_columns where index_name = 'index name ';

3) You can also use the PL/SQL developer tool.