Integrity constraint integrity constraints are used to enhance data integrity, Oracle provides five integrity constraints: Check Not Null Unique Primary Foreign Key Integrity Constraint is a rule that does not occupy any database space. Integrity Constraints In the Data Dictionary, use during SQL or PL / SQL. Users can indicate that constraints are enabled or disabled. When the constraint is enabled, he enhances the integrity of the data, otherwise, then, the constraint always exists in the data dictionary. Disable constraints, use Alter å ¥
ALTER TABLE TABLE_NAME DISABLE CONSTRAINT CONSTRAINT_NAME
or
ALTER TABLE Policies Disable consTRaint Chk_gender
If you want to re-enable the constraint:
Alter Table Policies Enable construint chk_gender
Delete constraint
Alter Table Table_name Drop Constraint Constraint_name
or
ALTER TABLE Policies Drop Constraint Chk_gender;
Check Constraints On the data column The check constraint requires a special Boolean condition or set the data column to true. The value of at least one data column is NULL, the Check constraint is used to enhance the simple business rules for data content in the table. User Use the Check Constrained Guarantee The consistency heck constraint can involve other data columns of the row of CHECK constraints but cannot involve other rows or other tables, or call functions sysdate, uid, user, useerenv. If the user's business rules need this type of data check, you can use the trigger. Check constraints do not protect the LOB data type data column and objects, nested tables, Varry, Ref, etc. Single data columns can have multiple Check constraints, and a Check constraint protects multiple data columns. Creating a table Check Constraint Using the CREATE TABLE statement, changing the constraints of the table use the ALTER TABLE statement. Grammar:
Constraint [constraint_name] check (condition);
The Check constraint can be created or added to a table constraint. When the Check constraints protect multiple data columns, you must use the table constraint syntax. The constraint name is optional and if this name does not exist, Oracle will generate a unique name starting with SYS_. example:
CREATE TABLE policies (policy_id NUMBER, holder_name VARCHAR2 (40), gender VARCHAR2 (1) constraint chk_gender CHECK (gender in ( 'M', 'F'), marital_status VARCHAR2 (1), date_of_birth DATE, constraint chk_marital CHECK (marital_status in ( 'S', 'm', 'd', 'w')));
NOT NULL Constraint Not Null Constraint Application On a single data column, and his protection data column must have a data value. Under the default situation, Oracle allows any columns to have NULL values. Certain business rules require a data column that must be valued, and the Not Null constraint will ensure that all data rows of the column value. example:
Create Table Policies (Policy_ID Number, Holder_Name Varchar2 (40) Not Null, Gnder Varchar2 (1), Marital_Status Varchar2 (1), Date_Of_birth Date Not Null); ALTER TABLE statement for NOT NULL is slightly different from other constraints.
Alter Table Policies Modify Holder_name Not Null
Unique constraint unique constraints can protect multiple data columns in the table to ensure that the data in any two lines in the protected data column is different. Uniqueness constraints are created together with the table, and after the uniqueness constraint is created, you can use the ALTER TABLE statement to modify. Grammar:
COLUMN_NAME DATA_TYPE CONSTRAINT CONSTRAINT_NAME UNIQUE
If unique constraints protect multiple data columns, uniqueness constraints are increased as a table constraint. The syntax is as follows:
ConsTRAINT CONSTRAINT_NAME (Column) Unique Using Index TableSpace (TableSpace_name) Storage
Uniqueness constraints are enhanced by a B-Tree index, so special features can be used in the USING substring, such as tablespace or storage parameters. The CREATE TABLE statement also creates a unique index to create a uniqueness constraint.
CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, vin VARCHAR2 (10), coverage_begin DATE, coverage_term NUMBER, CONSTRAIN unique_auto UNIQUE (policy_id, vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0));
Users can disable unreasonable constraints, but he still exists, disabling uniqueness constraints using ALTER TABLE statements
ALTER TABLE INSURED_AUTOS DISABLE CONSTRAIN UNIQUE_NAME
Delete uniqueness constraints, use the ALTER TABLE .... DROP Constrain statement
Alter Table Insured_Autos Drop Constrain Unique_Name
Note that the user cannot delete the uniqueness constraints of the tables with external keys. In this case, the user must first disable or delete the external key. Deleting or disabling uniqueness constraints typically delete the associated unique index, thereby reducing database performance. Regular deletion or disabling uniqueness constraints may result in loss of performance errors. To avoid this error, you can take the following steps: 1. Creating a non-unique index on the unique binding data column. 2. Add a uniqueness key constraint to the only primary key constraint. The primary key of the table can protect one or more columns, and the primary key constraint can work together with the Not Null constraint to each data column. The combination of NOT NULL constraints and unique constraints will ensure that the primary key is uniquely identified each row. Like a unique constraint, the primary key is enhanced by the B-Tree index. Creating primary key constraints Create together with the table if the table has been created, you can use the ALTER TABLE statement.
CREATE TABLE policies (policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY, holder_name VARCHAR2 (40), gender VARCHAR2 (1), marital_status VARCHAR2 (1), date_of_birth DATE); and uniqueness constraint, as if the primary key restraint plurality of data columns must be Create as a table constraint.
CREATE TABLE insured_autos (policy_id NUMBER, vin VARCHAR2 (40), coverage_begin DATE, coverage_term NUMBER, CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id, vin) USING INDEX TABLESPACE indexSTORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0));
Disable or delete the primary key must be used with the ALTER TABLE statement
Alter Table Policies Drop Primary Key;
or
ALTER TABLE POLICIES DISABLE PRIMARY Key;
Foreign Key Constraint External Key Constraint Protects one or more data columns to ensure that data of each data row contains one or more NULL values, or at the same manner, both of the protected data columns, both of which have primary key constraints or uniqueness constraints. The reference (primary key or unique constraint) constraint can protect the same table or protect different tables. Unique to the primary key and uniqueness constraints will not imply a B-Tree index. When processing an external key, we often use the childkeeple and sub-table (Child Table), the parent table represents a table that is referenced primary or unique constraints, and the child table represents a table that references primary keys and unique constraints. Creating an external key Using the CREATE TABLE statement, if the table has been established, use the ALTER TABLE statement.
CREATE TABLE insured_autos (policy_id NUMBER CONSTRAINT policy_fkREFERENCE policies (policy_idON DELETE CASCADE, vin VARCHAR2 (40), coverage_begin DATE, coverage_term NUMBER, make VARCHAR2 (30), model VARCHAR (30), year NUMBER, CONSTRAIN auto_fk FROEIGN KEY (make, model, Year) References Automobiles (Make, Model, Year) On Delete Set Null;
The ON Delete substring tells Oracle If the Parent Record is deleted, what is the child records. By default, it is prohibited to delete the parent record in the absence of subcord. The processing of the NULL value in the data column of the external key and NULL values in the data column of the external key may result in an unpredictable result. Oracle uses ISO Standar Match None rules to enhance external key constraints. This rule stages that if any external key role is included with a NULL value, then any data column that keeps the key is not match in the parent table. For example, in the parent table Automobiles, the primary key acts on the data column make, model, year, the user uses the table inSured_AUTOS with an external constraint to pointomomobiles, note that there is a data row in Insures_autos, a NULL value, this line of data has been Through constraints, even if the MAKE column is not displayed in the parent table Automobiles, as shown in the following table: Table 1 Automobilesmake Model Yearford Taurus2000Toyotacamry 1999
Table 2 Insured_AUTOS
Policy_idmake model year576 ford taurus 2000577 Toyotacamry 1999 578 Tucker Null 1949
Deferred constraint checking constraints two cases, one is to test whether the data meets the constraints after each statement, this test is called an immediate constraint test (IMMEDIATELY CHECKING), the other is in the transaction After the processing is completed, the data is checked is called the delay constraint test. In the default, the Oracle Constraint Test is an immediate test, and if the constraint is not satisfied, it will first be an error message, but the user can select the delay constraint inspection through the SET Constraint statement. The syntax is as follows:
Set constraint constraint_name | all defeerred | immediate -;
Sequences Oracle sequence is a continuous digital generator. Sequences are often used in artificial keywords, or sorted to data lines. Otherwise, the data line is disorderly. Like constraints, the sequence exists only in the data dictionary. The serial number can be set to rise, a decrease, and may not limit or reuse until a limit value. Create a sequence Using the set sequence statement.
Create Sequence [Schema] Sequence Keyword Keyword includes the value below:
KeyWord Description Start With defines the first number generated by the sequence. The default is a 1Increment BY defined serial number is rising or dropped. For a descending sequence increment BY, the minimum value of the minValue defines the sequence, this is the descending sequence Limit value. By default, this value is NominValue, NominValue, for the maximum number of serial sequences for sequencing to -10E26.maxValue sequences. This is the limit value in the ascending sequence, the default maxValue is NomaxValue, NomaxValue, for ascending to 10E26, for descending order -1. CYCLE Setting Series Values After reaching the restriction value, you can repeat the NOCYCLE Settings The sequence value cannot be repeated after the limit value is reached, which is the default setting. When trying to generate a value of MaxValue 1, an exception Cache defines the inner storage value occupied by the serial value. ?? 0NOCache forces the data dictionary update at each serial number, and ensure that the START WITH value must be equal to or greater than MinValue when the sequence is created between the sequence values. Delete sequence Using DROP SEQUENCE statement
The Drop Sequence Sequence_name Indexes Index is a data structure that improves query performance. In this part we will discuss how indexes improve query performance. Oracle provides the following indexes: B-Tree, Hash, Bitmap, etc. Index Types based on the original table, based on function-based index (Domain) index, actual application, is mainly B-Tree index and Bit map index, so we will focus on these two index types. The B-Tree Index B-Tree Index is the most common index. The index established under default conditions is the index of this type. The B-Tree index can be unique or unique, which can be a single (one column) or connection (multiple columns). The B-TREE index provides the best performance when retrieving a high base data column (the high-centron data column refers to the column has a lot of different values). A more efficient method is provided for removing smaller data B-Tree indexes than full table retrieval. However, when the range exceeds 10% of the table, it cannot improve the performance of the retrieved data. As the name is implied, the B-Tree index is based on binary tree, composed of branch block and leaf block, and branch dry blocks contains index columns (keywords) and another index. address. The leaves include a keyword and a RowID for each match in the table. The bitmap index bitmap index is mainly used for decision support system or static data, and the row-level lock is not supported. The bitmap index can be a simple (single column) or a connection (multiple columns), but most of the most is simple. Bitmap index is preferably used for low to the Cardinal column, where multi-bit map indexes on these columns can be used in conjunction with the AND or OR operator. The bitmap index uses the bitmap as the key value, and each of the data lines in the table contains True (1), false (0), or NULL value. The bitmap of the bitmap index stores in the page node of the B-Tree structure. The B-Tree structure makes the lookup bitmap very convenient and fast. In addition, the bitmap is stored in a compressed format, so the occupied disk space is much smaller than the B-Tree index. Synonyms (Synyms) is an alias for another data object. Public synonyms are for all users, relatively Private synonyms, only for account owners or accounts for authority or authority. Synonyms in the local database can represent tables, views, sequences, programs, functions, or packet and other data objects, can also represent objects of another database via links. Create synonymous syntax as follows: create [public] synynym synonym_name for [Schema.] Object [@db_link]; example:
Create public synynym policies for poladm.policies@prod; create synonym plan_table for system.plan_table;
