Oracle (9) Sequences and constraints

Sequence SEQUENCE
is also one of the database objects that generates some column numbers according to the specified rules.
A sequence is usually used to provide a value for a table's primary key.
Primary key: Usually each table has a primary key field, and the value of the field requires a non-null and unique
Use this field to determine each record in the table.

CREATE SEQUENCE Seq_emp_id_jie
START with 1
INCREMENT by 1;

CREATE SEQUENCE sequence Name
[START with I]--the first sequence value is I--default value 1
[INCREMENT by j]--step is J, next value is added J at a time--default value 1
[MAXVALUE N]--Maximum value is n
[MINVALUE N]--the bottom value is n
[CYCLE | Nocycle]-whether to continue producing serial numbers after the maximum or minimum value has been reached
[Cache]--used to specify that P data be pre-fetch to the cache to improve efficiency, the default value is 20;

The sequence provides two pseudo-columns:
Nextval: Gets the next number of the sequence that is returned when the first fetch is the number specified by STAR with.
It is the last acquired number plus the stepping get.
Nextval: Causes the sequence to step in and is not fallback.
Currval: Gets the current number of the sequence, which is the last number generated, and no stepping occurs.

The newly created sequence must be used once nextval before the currval can be used;
SELECT Seq_emp_id_jie. Nextval from dual;--Run once
SELECT Seq_emp_id_jie. Currval from dual;--last number

To delete a sequence:
DROP SEQUENCE Seq_emp_id_jie;

Test sequence:
CREATE TABLE Biao (
ID Number (4),
NAME VARCHAR2 (20)
);
INSERT into Biao (id,name)
VALUES (Seq_emp_id_jie. Nextval, ' JACK ');
SELECT * from Biao;
DROP TABLE Biao;

Indexed index

Syntax for creating indexes:
CREATE [UNIQUE] Index name
On table name (column name ...) ）;

CREATE INDEX Index_ename
On Emp_xiaojie (ename);
A compound is also called a multi-column index, which is based on multiple columns.
If you frequently use job and Sal ordering in the ORDER BY clause:
CREATE INDEX Emp_job_sal_jie
On Emp_xiaojie (job,sal);

SELECT * from Emp_xiaojie
ORDER by job,sal;--automatically apply Emp_job_sal_jie index

Function-based indexing
CREATE INDEX Emp_ename_upper_j
On Emp_xiaojie (UPPER (ename));

SELECT * from Emp_xiaojie
WHERE UPPER (ename) = ' KING ';

modifying and deleting indexes
If DML operations are frequently performed on the index, you need to define a rebuild index to increase the space of the index
Utilization, the syntax is as follows:
Rebuild Emp_ename_upper_j Index:
ALTER INDEX Emp_ename_upper_j REBUILD;

When an unreasonable use of a table results in decreased performance, the index is dropped:
DROP INDEX Index_ename;

Rational use of indexes to improve query efficiency:
To improve the efficiency of queries, the principles of creating and using indexes are:
* Create an index for a column that often appears in the WHERE clause
* To index A field that is often coveted after GROUP by, DISTINCT,
If you are building a composite index, the field order of the index must be the same as these keywords
Follow the same order.
* Create an index on a column that is frequently used as a join condition for a table.
* Do not build indexes on small tables.
* Limit the number of indexes on the table, not the more indexes the better.
* Delete less-used, unreasonable indexes.


Constraints:
Defined:
Constraint full name constraint condition, also called integrity constraint in DDL statement
Constraints are some data validation rules that are enforced on a data table when we execute
DML operations, the data must satisfy these rules and cannot be executed if not compliant.
Role:
Constraints can guarantee the integrity of the data in the table, and ensure the business logic between the data.
Type:
* nonempty constraint NOT NULL, abbreviated NN
* Uniqueness constraint unique, data in constraint fields cannot be unique, abbreviated UK
* PRIMARY KEY constraint primary key PK
* FOREIGN KEY constraint foreign key FK
* Check the constraint check CK

Uniqueness constraint: Unique UK
drop table Day9_zhang;

CREATE TABLE Day9_zhang (
ID Number (4),
Name Varchar2 Unique,-----The first way to add a constraint
INFO VARCHAR2 (30),
OWNER VARCHAR2 (10),
CONSTRAINT Owner_zhang UNIQUE (OWNER)-----The second way to add a constraint, to give the constraint a name

);

To cancel a constraint after a table is built:
ALTER TABLE Day9_zhang
Modify (NAME varchar2 ());----have a problem.

ALTER TABLE Day9_zhang drop constraint owner_zhang;---delete constraint
ALTER TABLE Day9_zhang DROP constraint system. sys_c0011567;
ALTER TABLE Day9_zhang DROP UNIQUE (NAME);
After building the table add:
ALTER TABLE Day9_zhang
Add constraint Info_zhang unique (INFO);

ALTER TABLE Day9_zhang
Add constraint Zhang_idzhang unique (ID);

INSERT INTO Day9_zhang
VALUES (1, ' QQ ', ' INFO ', ' 55 ');

INSERT INTO Day9_zhang
VALUES (1, ' QQ ', ' in ', ' 55 ');



PRIMARY KEY constraint PRIMARY key
A PRIMARY KEY constraint is functionally equivalent to a non-null constraint and a unique combination.
The primary key field can be a single field or a combination of multiple fields.
Role:
The data that is unique in the table that determines the row. A table only runs to establish a primary key constraint,
No restrictions on other constraints

drop table day9_zhang2;

CREATE TABLE Day9_zhang2 (
ID Number (4),
Name VARCHAR2,-----The first way to add a constraint
INFO VARCHAR2 (30),
OWNER VARCHAR2 (10),
CONSTRAINT Id_zhang PRIMARY KEY (ID)-----The second way to add a constraint, you can give the constraint a name

);

* The primary key should be data that is not meaningful to the system
* Never update the primary key so that the primary key has no other use other than the unique identity
* The primary key should not be dynamic data, such as timestamps
* The primary key should be auto-generated, not human intervention. Available sequences
* Primary key as far as possible on a single column

INSERT INTO Day9_zhang2
VALUES (Seq_zhang.nextval,...)


FOREIGN KEY constraints
A FOREIGN KEY constraint is defined on a field of two tables or two fields of a table for
Guaranteed Two-field relationships

For example, the Deptno column of the Emp_zhang table references the Deptno column of the DEPT table. The Dept table is the primary table or the parent table.
EMP is made from a table or a child table. The referenced field must be a primary key

FOREIGN KEY constraints on the maintenance of consistency, from two aspects of data constraints:
* Columns that define foreign keys from a table must be selected from the column values referenced by the primary table, or null
* When the value of the primary table reference column is referenced from the table, the row record of the primary table is not allowed to be deleted.
drop table Emp_hua;
CREATE TABLE Emp_hua (
ID Number (6),
Name VARCHAR2 (20),
SAL number (6,2),
DEPTNO Number (4)
);

ALTER TABLE Dept_zhang ADD constraint DEPT_ZHANG_PK primary key (DEPTNO);

ALTER TABLE Emp_hua
Add constraint EMP_HUA_DEPTNO_FK foreign key (DEPTNO)
REFERENces Dept_zhang (DEPTNO);
Reference--The meaning of the reference

INSERT INTO Emp_hua
VALUES (Seq.nextval, ' fools ', 1000,10);
INSERT INTO Emp_hua
VALUES (seq.nextval, ' rubbish ', 1000,null);
SELECT * from Emp_hua;

Delete from Dept_zhang where deptno=10;

rollback;----rollback operation

Performance reduction for FOREIGN KEY constraints:
If a foreign key is established on a table with frequent DML operations, each DML operation will cause the database to automatically
The corresponding table associated with the foreign key is checked, resulting in resource overhead. In addition, the foreign key determines the succession relationship of master and slave tables,
Sometimes it affects business logic.

To simplify development, maintain data without regard to foreign key constraints, as well as a large number of data DML operations
There is no need to consider the time-consuming foreign keys, and we do not necessarily need foreign key constraints when associating.


CHECK constraints:
Employee salary must be greater than 2000, increase constraint:
SELECT * from Emp_hua;
ALTER TABLE Emp_hua
Add constraint emp_hua_ck check (sal>999);
INSERT INTO Emp_hua
VALUES (seq.nextval, ' rice cooker ', 5555,20);
INSERT INTO Emp_hua
VALUES (SEQ. Nextval, ' Fat toot ', 5555,20);

Oracle (9) Sequences and constraints