/ *** constraint *** /
* If a constraint only acts on a separate field, you can define constraints in the field level, or you can define constraints in a table-level, but if a constraint acts on multiple fields, you must define constraints at the table level definition * You can name the constraint keyword, if not specified, Oracle will automatically establish the default name for constraints.
Define Primary Key Constraints (Single Field) Create Table Employees (Empno Number (5) Primary Key, ...)
Specify constraints Create Table Employees (Empno Number (5) constraint EMP_PK PRIMARY KEY, ...)
Defines primary key constraints (multiple fields, define constraints at the table level) create table employees (empno number (5), deptno number (3) 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 NULL constraint for a field (primary code field) with Primary Key constraints, defining the Primary Key constraints to specify storage locations and storage parameters for its index.
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 NULL constraints (only in the field-level definition not null constraint, can define multiple Not Null constraints in the same table) ALTER TABLE Employees Modify Deptno NOT NULL / NULL
unique constraints create table employees (empno number (5), ename varchar2 (15), phone varchar2 (15), email varchar2 (30) unique, deptno number (3) not null, constraint emp_ename_phone_uk unique (ename, phone))
Alter Table Employees Add Constraint Emp_uk Unique (Ename, Phone) Using Index TableSpace IndX
Define the repetition values in the field of UNIQUE constraints, which can define a unique constraint for one or more fields, so unique can also include a null value on the field level in a field level, and can include a null value on the UniqueD constraint.
Foreign Key Constraint
* Defined with the field of the reference code field in the FOREIGN Key constraint or NULL value * can define the external code field of the Foreign Key constraint for one or more fields. And the corresponding reference code fields can exist in the same table, which is called "self-reference" * to simultaneously define the same field to simultaneously define Foreign Key constraints and Not Null constraints.
The field defined by the Foreign Key constraint is called "external code field", which is called "Reference Code Field" by the FORGIEN KEY constraint reference, and the reference code must be a master code or a unique code, and the table containing the external code is a sub-table. A representation of a reference code is a parent table.
A: CREATE TABLE Employees (....., Deptno Number (3) Not null, constraint EMP_DEPTNO_FK Foreign Key (DePTNO) References DEPT (DEPTNO) If the external code in the sub table is the same as the reference code in the primary table Name, you can write: B: Create Table Employees (....., Deptno Number (3) Not null constraint EMP_DEPTNO_FK REFERENCES DEPT)
Note: The above example (b) did not add a message behind NOT NULL, because the contraint of this sentence is followed behind the column deptno, which belongs to the column definition, so there is no need to specify. In A case, the table is defined, you need to indicate that column, so you have to add a message, you can't define it later, you can also write:
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 in the end, then see two 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 (30) unique, deptno number (3) not null, constraint emp_pk primary key (empno, ename), constraint emp_phone_uk unique (Phone))
Add a Foreign Key Constraint (Multi-Field / Table Level) ALTER TABLE EmployeesAdd ConsTRAINT EMP_JOBS_FK Foreign Key (Job, Deptno) References Jobs (Jobid, Deptno) on Delete Cascade
Change the reference behavior definition of Foreign Key Constraint (Delete No Action), default is delete on action
Quote Behavior (When a record in the primary table is deleted, determine how the external code field in the word table): DELETE CASCADE: Delete all related records in the sub-table DELETE SET NULL: Set the external code field value of all related records to Nulllete No Action: Don't do anything
First delete the original foreign key constraint, add a restraint 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 Constraints * You must reference one or more fields in the table in the expression of Check, and the calculation result of the expression must be a Boolean value * can define more than the table level or field level definition * CHECK constraint, can also define Not NULL constraints Create Table Employees (Sal> 0)) ALTER TABLE EmployeesAdd ConsTRAINT EMP_SAL_CK2 CHECK (SAL <20000)
Delete constraint
ALTER TABLE DEPT DROP UNIQUE (DNAME, LOC) - Specifies the definition of constraints ALTER TABLE DEPT DROP CONSTRAINT DEPT_DNAME_LOC_UK - Specify a constraint name
When deleting constraints, the index corresponding to the constraint will be deleted by default. If you want to keep an index, use Keep Index Keyword ALTER TABLE Employees Drop Primary Key Keep Index
If the constraint to delete is being referenced by other constraints, specify the Cascade keyword through the alter table..drop statement to delete the reference to the reference.
When using the following statement in deleting the Primary Key constraint in the DEPT table, the Foreign Key constraint that references this constraints will be deleted at the same time: ALTER TABLE DEPT DROP PRIMARY KEY CASCADE
Disable / activate the constraint (disable / activate the constraint will cause the operation to delete and rebuild the index) alter table employees disable / enable unique emailalter table employees disable / enable constraint emp_ename_pkalter tabel employees modify constraint emp_pk disable / enablealter tabel employees modify constraint emp_ename_phone_uk disable / enable
If the Foreign Key constraint is being referenced to UNIQUE or Primary Key constraints, these Unique or Primary Key constraints cannot be disabled, and you can disable the Foreign Key constraint, then disable unique or primary key constraints; or can be in Alter Table ... Disable Specify the Cascade keyword in the statement, which will disable the Foreign Key constraints that are disabled to unique or Primary Key, such as: ALTER TABLE Employees Disable Primary Key Cascade
Constrained Data Dictionary All_constraints / DBA_Constraints / User_constraints Constrained Basic Information, including constraints, type, status (constraint type: c (Check constraint), P (main code constraint), u (unique code constraint) )) ALL_CONS_COLUMNS / DBA / User Constrained Field Information
/ *** index *** /
Indexes and corresponding tables should be in different tablespaces, Oracle can read data on different hard drives in parallel, avoiding generating I / O collision B tree index: stores the value of the index field in the leaf node of the B tree and RowID . The only index and unique index are just for B-tree indexes. Oracle allows for a composite index containing 32 fields.
Index Creating Policy 1. Creating Index after importing data 2. You don't need to create indexes for small tables. 3. For fields (such as gender fields), bitmaphe, restrictions in the table. Number 5. Set the appropriate PCTFREE value for the index 6. The tablespace of the store index is best set separately.
Create a unique index create index EMP_ENAME ON Employees (Ename) TableSpace UserStorage (...) 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;
Create a reseller index Create Unique Index Order_reinx on Orders (Order_num, Order_Date) TABLESPACE UserSreverse;
Creating a function index (function index can be a normal B tree index, or a bitmap index EMP_SUBSTR_EMPNOON EMPLOYEES (Substr (Empno, 1, 2)) TABLESPACE USERS;
Modify the index storage parameter (similar to the table, the initial and minxtents parameters can no longer change after indexing) ALTER INDEX EMP_ENAME Storage (PctinCrease 50);
Since the index auto-established by Oracle is usually unknown because of the defined constraints, modifications to such indexes often use the ALTER TABLE ..using index statement, not the ALTER INDEX statement
Use the following statement to modify the PCTFree parameters of the index corresponding to the primary key in the Employees table to a 5 RALTER TABLE Employees Enable Primary Key Using Index PctFree 5;
Clean up index fragmentation 1. Merge index (just simply simply combined with storage fragments in the bone node, does not change the physical organizational structure of the index) Alter Index EMP_PK COALESCE;
2. Reconstruction of the index (not only eliminating the storage debris, but also change the all store parameters settings of the index, and can move the index to other tablespaces, and the reconstruction index is actually re-establishing a new index in the specified table space. , Then delete the original index) alter index EMP_PK REBUILD;
Delete Index DROP INDEX EMP_ENAME;
If the index contains damaged data blocks, or contains too much storage debris, you need to delete this index, then rebuild it. If the index is automatically generated by Oracle when creating constraints, it is possible to disable constraints or delete constraints. To delete the corresponding index. Oracle automatically deletes all indexes associated with the table when deleting a table.
Index data dictionary all_indexes / dba_indexes / user_indexes indexing basic information all_ind_columns / dba_ind_columns / user_ind_columns index corresponding field information
