Oracle 11g Study Notes 10_31 (2) _ maintain data integrity

For tables with massive data volumes, index creation and query are much faster than those without indexes.
Maintain data integrity

Description: data integrity is used to ensure that database data complies with certain commercial and logical rules. In Oracle, data integrity can be implemented using constraints, triggers, and applications (processes, functions). In these three methods, constraints are easy to maintain and have the best performance, therefore, it is the first choice to maintain data integrity.
I. Constraints

Constraints are used to ensure that database data meets specific business rules. In Oracle,Constraints include: not null, unique, primary key, foreign key, and check five.Type.
. Not null (not empty)
If the column defines not null, data must be provided to the column when data is inserted.
. Unique (unique)
When a unique constraint is defined, the column value cannot be repeated, but it can be null.
. Primary Key (primary key)
It is used to uniquely identify the data of the table rows. When the primary key constraint is defined, this column cannot be repeated and cannot be null.
Note:A table can have at most one primary key.But there can be multiple unqiue constraints.
. Foreign key (foreign key)
It is used to define the relationship between the master table and the slave table. Foreign key constraints must be defined in the slave table, and the master table must have primary key constraints or unique constraints. After defining foreign key constraints, the foreign key column data must exist in the primary key column or be null.
. Check
Used to force the row data to meet the conditions. If the check constraint is defined on the Sal column and the Sal column value must be between and, an error will be reported if the weak country is not between and.

* Case
Store sales system table design case?
The database of an existing store records the customer's shopping status and consists of the following three tables: goods Goods
(Product NO. goodsid, product name goodsname, unit price unitprice, product category,
Supplier provider );
Customer (customer ID customerid, name, address, email,
Sex, ID card cardid );
Purchase (customer number customerid, product number goodsid, number of purchased Nums );
Use SQL to complete the following functions:
1. Create a table and declare it in the definition:
(1). Primary and Foreign keys for each table;
(2) the customer's name cannot be null;
(3) The unit price must be greater than 0, and the purchased quantity must be between 1 and 30;
(4) The email cannot be repeated;
(5) The customer's gender must be male or female. The default gender is male;

Create Table goods (goodsid char (8) primary key, -- set the primary key goodsname varchar2 (30), unitprice number () Check (unitprice> 0), category varchar2 (8 ), provider varchar2 (30); Create Table customer (customerid char (8) primary key, -- Set primary key name varchar2 (50) not null, address varchar2 (50 ), email varchar2 (50) Unique, sex char (2) default 'male' check (sex in ('male', 'female '), -- a char can store half Chinese characters, two char characters can store one Chinese character. Cardid char (18); Create Table purchase (customerid char (8) References customer (customerid), -- point to customerid goodsid char (8) References goods (goodsid) of the customer table ), nums number (5) Check (Num between 1 and 30 ));

* Add Constraints
Store sales system table design case (2 )?
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 must use the modify option, while adding the other four constraints to use the Add option.
1. Enter a product name.

alter table goods modify goodsName not null;

2. Duplicate ID cards are not allowed to be added

alter talbe customer add constraint cardIdUnique unique(cardId);

Add constraint cardidunique indicates adding a constraint named cardidunique, and constraint indicates the meaning of the constraint.
3. The customer's addresses can only be 'tianhe ', 'yuexiu', 'haizhu ', 'panyu', 'tsuen Wan ', and 'conghua'

Alter table customer add constraint addrcheck check (address in ('tianhe ', 'haizhu', 'yuexiu ', 'tsuen Wan', 'conghua '));

* Delete Constraints
When you no longer need a constraint, you can delete it.
Alter table Table Name drop constraint name;
An error may occur when deleting the primary key constraint, for example
Alter table Table Name 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,
Alter table Table Name drop primary key cascade;

* Display 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_constraints where table_name = 'table name ';

2. Display constraint Columns
You can query the data dictionary view user_cons_columns to display the table column information corresponding to the constraint.

Select column_name, position from user_cons_columns where constraint_name = 'constraint name ';

3. You can directly use the PL/SQL developer tool to view
Ii. Table-Level Definition and column-Level Definition

* Column-Level Definition
Column-level definition defines constraints while defining Columns

Create Table purchase (customerid char (8) References customer (customerid), -- point to customerid goodsid char (8) References goods (goodsid), Nums number (5) of the customer table) check (Num between 1 and 30 ));

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

Create Table goods (goodsid char (8), goodsname varchar2 (30), unitprice number (10, 2), category varchar2 (8), provider varchar2 (30 ), constraint pk_goodsid primary key (goodsid), -- set the primary key constraint chk_price check (unitprice> 0) -- the price is greater than zero );

There is no difference between the two definitions. If a column-level definition is used, the system will randomly give the constraint a name because there is no name for the constraint. This is the difference.
Iii. Index

* Introduction
Indexes are suitable for data objects that accelerate data access. Using indexes can greatly reduce the number of I/O operations and improve data access performance. There are many types of indexes:
Why does the query speed increase after an index is added?
* Create an index
. Single Column IndexIs an index created based on a single column, such
Create Index name on table name (column name)
. Composite Index
An index is based on two or more columns. Multiple indexes can exist on the same table, but different combinations of columns are required. For example

create index emp_idxl on emp(ename,job);create index emp_idxl on emp(job,ename);

* Indexing principles
1) InLarge tableIt makes sense to create an index on
If the table data is small, it will be meaningless, that is, it will waste our indexing time and space.
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.

* Index Defect Analysis
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.
Improper indexes will not help, but will reduce system performance. Because a large number of indexes take more time to insert, modify, and delete than no index.