Oracle Learning Notes indexes and constraints

/*** constraint ***/

* If a constraint is only used for a separate field, you can define a constraint at the field level, or you can define a table-level

But if a constraint acts on more than one field,
Constraints must be defined at the table level
* When defining constraints, you can name the constraint with the constraint keyword, and if not specified, Oracle will automatically

Establish a default name for the constraint

Define PRIMARY KEY constraint (single field)
CREATE TABLE Employees (empno number (5) primary key,...)

Specify constraint name
CREATE TABLE Employees (empno number (5) Constraint EMP_PK primary key,...)

Defining PRIMARY KEY constraints (multiple fields, defining constraints at the table level)
CREATE TABLE Employees
(empno number (5),
Deptno Number (3) is not NULL,
Constraint EMP_PK primary KEY (Empno,deptno)
Using index tablespace indx
Storage (initial 64K
Next 64K
)
)

Oracle automatically establishes a unique index and a not for fields with the PRIMARY KEY constraint (the Master Code field)

A null constraint, which can be indexed when the primary KEY constraint is defined
Specify storage location and storage parameters

ALTER TABLE employees add primary key (EMPNO)
ALTER TABLE employees ADD constraint EMP_PK primary key (EMPNO)
ALTER TABLE employees ADD constraint EMP_PK primary key (Empno,deptno)

Not a null constraint (a NOT NULL constraint can be defined at the field level only, and multiple not NULL is defined in the same table)

Beam
ALTER TABLE employees modify DEPTNO not Null/null

Unique constraint
CREATE TABLE Employees
(empno number (5),
ename VARCHAR2 (15),
Phone VARCHAR2 (15),
Email varchar2 (+) Unique,
Deptno Number (3) is not NULL,
Constraint Emp_ename_phone_uk unique (ename,phone)
)

ALTER TABLE Employees
Add constraint Emp_uk unique (ename,phone)
Using index tablespace indx

A field that defines a unique constraint cannot contain duplicate values, and you can define a unique constraint for one or more fields.

Therefore, unique can also be defined at the table level at the field level,
You can include a null value on a field that is uniqued constrained.

FOREIGN KEY constraint

* The field defined as the FOREIGN KEY constraint can contain only the value of the Reference Code field in the corresponding other table or

Null value
* FOREIGN KEY constraints can be defined for the combination of one or more fields
* The external Code field that defines the foreign key constraint and the corresponding reference Code field can exist in the same table,

This is referred to as a "self-reference"
* FOREIGN KEY constraints and NOT NULL constraints can be defined for the same field

The field that defines the foreign key constraint is called an "External code field", and the field referenced by the Forgien key constraint is called "

Reference Code field ", the reference code must be a main code or a unique code, the table containing the external code is called a child table,
The table that contains the reference code is called the parent table.

A:
CREATE TABLE Employees
(.....,
Deptno Number (3) is not NULL,
Constraint EMP_DEPTNO_FK foreign key (DEPTNO)
References Dept (DEPTNO)
)

If the external code in the child table has the same name as the reference code in the primary table, it can be written as:
B:
CREATE TABLE Employees
(.....,
Deptno Number (3) not NULL
Constraint EMP_DEPTNO_FK References Dept
)

Attention:
The above example (B) is not null followed by a comma, because the contraint of this sentence is followed by the column

Deptno, which belongs to the column definition, does not need to specify the column. In the case of a table definition, it is necessary to specify the

columns, so add commas, cannot be defined behind columns, and can be written as:

CREATE TABLE Employees
(Empno char (4),
Deptno char (2) NOT NULL constraint EMP_DEPTNO_FK references dept,
ename VARCHAR2 (10)
)
Table Definition contraint can only be written at the end, see two more examples:

CREATE TABLE Employees
(empno number (5),
ename VARCHAR2 (10),
Deptno char (2) NOT NULL constraint EMP_DEPTNO_FK references dept,
Constraint EMP_PK primary KEY (Empno,ename)
)

CREATE TABLE Employees
(empno number (5),
ename VARCHAR2 (15),
Phone VARCHAR2 (15),
Email varchar2 (+) Unique,
Deptno Number (3) is not NULL,
Constraint EMP_PK primary KEY (Empno,ename),
Constraint Emp_phone_uk unique (phone)
)

Add FOREIGN KEY constraint (multi-field/table-level)
ALTER TABLE Employees
Add constraint EMP_JOBS_FK foreign key (JOB,DEPTNO)
References Jobs (JOBID,DEPTNO)
ON DELETE Cascade

Change the reference behavior of the FOREIGN KEY constraint definition (delete cascade/delete set Null/delete No

Action), the default is delete on action

Referential behavior (determines how to handle external code fields in a Word table when a record in the primary table is deleted):
Delete Cascade: Deletes all related records in the child table
Delete Set NULL: Sets the external Code field value of all related records to NULL
Delete No action: do nothing

Delete the original foreign KEY constraint before adding the constraint
ALTER TABLE Employees DROP CONSTRAINT EMP_DEPTNO_FK;
ALTER TABLE Employees ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO)

REFERENCES Dept (DEPTNO) on DELETE CASCADE;

Check Constraint
* One or more fields in the table must be referenced in the expression in the check constraint, and the result of the expression evaluates

Must be a Boolean value
* Can be defined at the table level or at the field level
* Multiple CHECK constraints can be defined on the same field, and NOT NULL constraints can also be defined

CREATE TABLE Employees
(Sal number (7,2)
Constraint emp_sal_ck1 Check (sal > 0)
)

ALTER TABLE Employees
Add constraint emp_sal_ck2 Check (Sal < 20000 br style= ' Font-size:14px;font-style:normal;font-weight:normal;color: RGB (0, 0, 0); '/>
Delete Constraint

ALTER TABLE Dept Drop Unique (dname,loc)--Specify the definition of the constraint
ALTER TABLE DEPT DROP constraint Dept_dname_loc_uk--Specify constraint name

When you delete a constraint, the index corresponding to the constraint is deleted by default, and if you want to preserve the index, use the Keep index key

Word
ALTER TABLE employees drop PRIMARY key Keep index

If the constraint to be removed is being referenced by another constraint, alter TABLE: Specify cascade off in the drop statement

A key word can delete a constraint that references it at the same time

Use the following statement to delete the primary KEY constraint in the Dept table while deleting the other tables that reference the

FOREIGN KEY constraints for constraints:
ALTER TABLE Dept DROP PRIMARY KEY Cascade

disabling/activating constraints (disabling/activating constraints can cause operations to delete and rebuild indexes)
ALTER TABLE employees disable/enable unique Email
ALTER TABLE employees disable/enable constraint EMP_ENAME_PK
Alter Tabel employees modify constraint emp_pk disable/enable
Alter Tabel employees modify constraint Emp_ename_phone_uk disable/enable

If a foreign key constraint is referencing a unique or PRIMARY KEY constraint, you cannot disable these unique or

PRIMARY key Constraint,
You can then disable the FOREIGN KEY constraint before disabling the unique or PRIMARY KEY constraint, or you can

ALTER TABLE ... DISABLE
The Cascade keyword is specified in the statement, which disables the unique or PRIMARY KEY constraint while disabling those

Use their foreign key constraints, such as:
ALTER TABLE employees DISABLE PRIMARY KEY cascade

Constrained data dictionary
Basic information about the all_constraints/dba_constraints/user_constraints constraints, including the constraints

Name, type, status
(Constraint type: C (check constraint), P (main code constraint), R (external code constraint), U (unique code constraint))
All_cons_columns/dba/user constraint corresponding field information

/*** Index ***/

The index and the corresponding table should be in different table spaces, and Oracle can read data on different hard disks in parallel

, you can avoid I/O conflicts
B-Tree Index: Stores the value of the indexed field in the leaf node of the B-tree with ROWID.
Both unique and non-unique indexes are only for the B-tree index.
Oracle allows a composite index with up to 32 fields

Index creation Policy
1. Import data before creating an index
2. You do not need to create indexes for very small tables
3. Bitmap indexing should be made for fields that have a small range of values (such as gender fields)
4. Limit the number of indexes in a table
5. Set the appropriate Pctfree value for the index
6. The table space where the index is stored is best set individually

Create a non-unique index
Create index Emp_ename on employees (ENAME)
Tablespace users
Storage (...)
Pctfree 0;

Create a unique index
Create unique index emp_email on employees (email)
Tablespace users;

Create a bitmap index
Create bitmap index emp_sex on employees (sex)
Tablespace users;

To create a reverse-order index
Create unique index Order_reinx on orders (order_num,order_date)
Tablespace users
Reverse

Create a function index (the function index can be either a normal B-tree index or a bitmap index)
CREATE INDEX Emp_substr_empno
On employees (SUBSTR (empno,1,2))
Tablespace users;

Modify index storage parameters (similar to tables, initial and minextents parameters cannot be changed after indexing is established)
Alter INDEX EMP_ENAME Storage (pctincrease 50);

Because indexes that are automatically established by Oracle when defining constraints are often unknown, modifications to such indexes are often

Is the use of ALTER TABLE. Using the index statement instead of the ALTER INDEX statement

Use the following statement to modify the Pctfree parameter of the index for the primary KEY constraint in the Employees table to 5
ALTER TABLE employees enable primary key using index Pctfree 5;

Clean Up Index fragmentation
1. Merging the indexes (simply merging the storage fragments from the B leaf nodes does not alter the index's physical

Organizational structure)
Alter index EMP_PK COALESCE;

2. Rebuild the index (not only to eliminate storage fragmentation, but also to change all of the index's storage parameter settings, and you can

Index is moved to a different table space, rebuilding the index
is actually re-establishing a new index in the specified tablespace and then deleting the original index)
Alter index EMP_PK rebuild;

Delete Index
Drop index Emp_ename;

If the index contains a corrupted block of data, or contains too much storage fragmentation, you need to delete the index first, and then

And then rebuild it.
If an index is generated automatically by Oracle when creating constraints, you can disable the constraint or remove the constraint by using the method to

Deletes the corresponding index.
When you delete a table, Oracle automatically deletes all indexes associated with that table.

Index data dictionary
All_indexes/dba_indexes/user_indexes Basic information for indexes
All_ind_columns/dba_ind_columns/user_ind_columns The field information for the index

Oracle Learning Notes indexes and constraints