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 take up 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 statements
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 constraint
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, and the Check constraint is used to enhance the simple business rules for data content in the table. The user uses the Check Constrained to ensure the consistency of the data rule. The Check constraint can involve the other data columns of the CHECK constraint but cannot involve other rows or other tables, or call the function 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
Uniqueness constraints can protect multiple data columns in the table to ensure that the data in any two rows in the protected data column are 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 steps below:
1. Create a non-unique index on the data column of unique restrictions.
2, add uniqueness constraints
Primary Key Constraint
The table has a unique 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;
External key constraint
External key constraints protects one or more data columns to ensure that the data of each data line contains one or more NULL values, or both the primary key constraint or uniqueness constraints simultaneously on the protected data column. 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.
External keys and NULL values
The processing of NULL values in the data column of external key constraints may result in unpredictable results. 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 follows:
Table 1 Automobilesmake Model Year Ford Taurus 2000 Toyota Camry 1999
Table 2 Insured_AUTOS
Policy_id Make Model Year 576 Ford Taurus 2000 577 Toyota Camry 1999 578 Tucker Null 1949
Deferred constraint checking
There are two cases of constraining check points, one is to test whether the data meets the constraint conditions after each statement, and this test is called an immediate constraint checking, and the other is to check the data after the transaction is completed. It is a 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 (SEQUENCES)
The 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 1 incrtainty defined serial number is rising or dropped. For a descending sequence increment By is a minimum of the minaValue defined sequence, this is a descended sequence sequence The limit value is limited. By default, this value is NominValue, NominValue, for ascending to 1, for the maximum number of -10E26. MaxValue sequence can be generated. 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 the value of MaxValue 1, an exception Cache defines the size of the memory block occupied by the serial value. The default value is 20 NOCache to force the data dictionary update when each serial number is generated, and guaranteed between sequence values There is no interval when creating a sequence, the Start with value must be equal to or greater than MinValue.
Delete sequence Using DROP SEQUENCE statement
DROP SEQUENCE SEQUENCE_NAME
Indexes
Index is a data structure that can improve query performance, in which part we will discuss how indexes improve query performance. Oracle provides the following indexes:
Index types such as B-Tree, Hash, Bitmap, based on the index of the original table, based on the function-based index (DOMAIN) index
The actual application is mainly B-TREE index and bitmap index, so we will focus on these two index types.
B-Tree index
The B-Tree index is the most common index. The index established under default 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. Bitmap index
The 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)
The profile 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 synonyms syntax as follows:
Create [public] synonym synonym_name for [schema.] Object [@db_link]; example:
Create Public Synonym Policies for Poladm.Policies@prod;
Create Synynym Plan_Table for System.plan_Table;
