Oracle integrity constraints

Oracle integrity constraints are used to enhance data integrity. Oracle provides five integrity constraints: Check not null Unique Primary Foreign key integrity constraints are a rule, it does not occupy any database space. Integrity constraints exist in data dictionaries and are used during SQL or PL/SQL Execution. You can specify whether the constraint is enabled or disabled. When the constraint is enabled, it enhances data integrity. Otherwise, the constraint is always in the data dictionary. To DISABLE constraints, use the ALTER statement alter table table_name disable constraint constraint_name; or alter table policies disable constraint chk_gender to re-enable constraints: alter table policies enable constraint chk_gender Delete constraint alter table table_name drop constraint constraint_name or alter table policies drop constraint chk_gender; the Check constraint requires a special Boolean condition for the Check constraint on the data column or sets the data column to TRUE. The value of at least one data column is NULL, the Check constraint is used to enhance the simple commercial rules of table data content. You can use the Check constraint to ensure data rule consistency. The Check constraint can involve other data columns that belong to the same Check constraint but cannot involve other rows or other tables, or call the function SYSDATE, UID, USER, USERENV. If your business rules require such data checks, you can use triggers. The Check constraint does not protect LOB data columns and objects, nested tables, VARRY, and ref. A single data column can have multiple Check constraints, and one Check constraint can protect multiple data columns. The Check constraint for creating a TABLE uses the create table statement, and the alter table statement is used to change the constraints of a TABLE. Syntax: CONSTRAINT [constraint_name] CHECK (condition); Check constraints can be created or added as a table CONSTRAINT. When Check constraints protect multiple data columns, you must use the table CONSTRAINT syntax. The constraint name is optional. If the name does not exist, oracle generates a unique name starting with SYS. Example: create table indexes ies (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_mar1_check (marital_status in ('s', 'M', 'D', 'w '))); not null constraint applies to a single data column, and the data column protected by it must have a data value. By default, ORACLE allows any column to have a NULL value. Some commercial rules require a data column to have a value. The not null constraint ensures that all data rows in the column have a value. Example: create table indexes ies (policy_id NUMBER, holder_name VARCHAR2 (40) not null, gender VARCHAR2 (1), marital_status VARCHAR2 (1), date_of_birth date not null ); the alter table statement for not null is slightly different from other constraints. Alter table policies MODIFY holder_name not null Unique constraint (Unique constraint) Unique constraint can protect multiple data columns in a TABLE and ensure that the data of any two rows in the protected data column is NOT the same. The Uniqueness constraint is created with the TABLE. After the uniqueness constraint is created, you can use the alter table statement to modify it. Syntax: column_name data_type CONSTRAINT constraint_name UNIQUE if the uniqueness CONSTRAINT protects multiple data columns, the uniqueness CONSTRAINT must be added as a table CONSTRAINT. Syntax: CONSTRAINT constraint_name (column) unique using index tablespace (tablespace_name) STORAGE (stored clause) The uniqueness CONSTRAINT is enhanced by a B-tree INDEX, therefore, you can use special features such as tablespace or storage parameters for the index in the USING substring. The create table statement creates a unique index for the target data column while creating the 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 1 m next 10 m pctincrease 0); you can disable non-sexual constraints, but they still exist, DISABLE uniqueness constraint use alter table statement alter table insured_autos disable constrain unique_name; Delete uniqueness constraint, use alter table .... drop constrain Statement Lter table insured_autos drop constrain unique_name; note that you cannot delete the uniqueness constraint of a TABLE pointed to by an external key. In this case, you must first disable or delete the external key (foreign key ). Deleting or disabling the uniqueness constraint usually deletes the associated unique index at the same time, thus reducing the database performance. Frequent deletion or disabling of uniqueness constraints may result in performance errors caused by loss of indexes. To avoid this error, take the following steps: 1. Create a non-unique index on the data column protected by the uniqueness constraint. 2. Add a unique Primary Key constraint. The table has a unique Primary Key constraint. The primary key of a table can protect one or more columns. The primary key constraint can work with the not null constraint on each data column. The combination of not null constraints and uniqueness constraints ensures that the primary key uniquely identifies each row. Like the uniqueness constraint, the primary key is enhanced by the B-tree index. Use the create table statement to CREATE a primary key constraint with the TABLE. If the TABLE has been created, use the alter table statement. Create table indexes ies (policy_id number constraint pk_policies primary key, holder_name VARCHAR2 (40), gender VARCHAR2 (1), marital_status VARCHAR2 (1), date_of_birth DATE); same as the uniqueness CONSTRAINT, if the primary key constraint protects multiple data columns, it must be created 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 1 m next 10 m pctincrease 0); to disable or delete a primary key, you must use alter table indexes ies drop primary key together with the alter table statement; or alter table policies disable primary key; external key constraint (Foreign KEY constraint) protects one or more data columns and ensures the packets of each data row Contains one or more null values, or a primary key constraint or uniqueness constraint on the protected data column. The reference (primary key or uniqueness constraint) constraint can protect the same table or different tables. Unlike primary keys and uniqueness constraints, external keys do not implicitly create a B-tree index. When processing external keys, we often use the terms "parent table" and "child table". The parent table indicates the table with the referenced primary key or uniqueness constraint, A sub-table is a table that references the primary key and uniqueness constraints. Use the create table statement to CREATE an external key. If the TABLE has been created, use the alter table statement. Create table indexes (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 sub-string tells ORACLE what to do after a parent record is deleted. By default, deleting a parent record is prohibited if the child record still exists. Processing of NULL values of the external key and NULL value in the data column protected by the external key constraints may produce unpredictable results. ORACLE uses the ISO standar Match None rule to enhance external key constraints. This rule specifies that if any data column with an external key has a NULL value, any data column with this key retained has no matching value in the parent table. For example, in the parent table AUTOMOBILES, the primary key acts on the MAKE, MODEL, and YEAR columns of the data column. The user-used table INSURED_AUTOS has an external constraint pointing to AOTOMOBILES, note that in INSURES_AUTOS, the MODEL column of the Data row is NULL. This row of data has passed the constraints check, even if the MAKE column is not displayed in the parent table AUTOMOBILES, as shown in the following table: table 1 automobiles make 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 latency Constraint check (Deferred Constraint Checking) the constraint test is divided into two situations: one is to check after each statement ends. Check whether the data meets the constraints. This test is called the immediately checking. The other is to test the data after the transaction is processed. By default, the Oracle CONSTRAINT check is an immediate check (immediately checking). If the constraints are not met, an error message is returned first. However, you can use the set constraint statement to select a latency CONSTRAINT check. Syntax: set constraint constraint_name | all defeerred | IMMEDIATE --; Sequence Oracle sequence is a continuous digital generator. Sequences are often used for human keywords or sorting data rows. Otherwise, data rows are unordered. Like constraints, sequences only exist in data dictionaries. The serial number can be set to increase or decrease, and can be used without restrictions or repeated until a limit value. Use the set sequence statement to create a SEQUENCE. Create sequence [schema] sequence KEYWORD includes the following values: KEYWORD describes the first number generated BY the start with sequence. The default value is 1 increment by, which defines whether the serial number is increased or decreased, for a descending sequence whose increment by is negative, MINVALUE defines the minimum value that can be generated BY the sequence, which is the limit value in the descending sequence. By default, this value is NOMINVALUE and NOMINVALUE. For Ascending Order 1, for descending order-10E26. MAXVALUE, the maximum number that can be generated by the sequence. This is the limit value in the ascending sequence. The default value is NOMAXVALUE and NOMAXVALUE. For Ascending Order, the value is 10E26, and for descending order, the value is-1. The CYCLE sequence value can be repeated after it reaches the limit value. The sequence value cannot be repeated after it reaches the limit value. This is the default setting. When you try to generate a MAXVALUE + 1 value, an exception will be generated. The CACHE defines the size of the memory block occupied by the sequence value. The default value is 20. NOCACHE forces Data Dictionary Update every time the serial number is generated, there is no interval between sequence values. When creating a sequence, the start with value must be equal to or greater than MINVALUE. Deleting a SEQUENCE using the drop sequence statement drop sequence sequence_name index (INDEXES) is a data structure that improves query performance. In this section, we will discuss how INDEXES can improve query performance. ORACLE provides the following indexes: B-Tree, hash, bitmap, and other index types based on the original table index function-based index Domain) in actual application of indexes, we mainly use B-Tree indexes and bitmap indexes. Therefore, we will focus on these two types of indexes. B-Tree index is the most common index. The index created by default is this type of index. B-Tree indexes can be unique or non-Unique. They can be single (based on one column) or connected (multiple columns ). B-Tree indexes provide the best performance when retrieving a high-base data column (a high-base data column refers to a column with many different values. B-Tree indexes of small data are more effective than full table search. However, when the check range is more than 10% of the table, the performance of data retrieval cannot be improved. As the name implies, the B-Tree index is based on binary trees and consists of branch blocks and leaf blocks, the branches contain the index column (keyword) and the address of another index. Leaf blocks contain keywords and the ROWID of each matching row in the table. Bitmap index Bitmap indexes are mainly used to support system or static data and do not support row-level locking. Bitmap indexes can be simple (Single Column) or connected (multiple columns), but most of them are simple in practice. Bitmap indexes are best used in cardinality columns, where multi-Bitmap indexes can be used in combination with the and or operator. Bitmap indexes use bitmaps as key values. bitmaps of each data row in a table contain values such as TRUE (1), FALSE (0), or NULL. Bitmap indexes are stored in the page nodes of the B-Tree structure. The B-Tree structure makes it very convenient and fast to find bitmaps. In addition, bitmap is stored in a compressed format, so the disk space occupied is much smaller than that of B-Tree indexes. Synonyms is an alias for another data object. The public synonym is for all users. The private synonym is for only the object owner or the account granted permissions. Synonyms in a local database can represent data objects such as tables, views, sequences, programs, functions, or packages, or objects in another database through links. Syntax FOR creating Synonyms: CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object [@ db_link]; example: create public synonym policies FOR poladm. policies @ prod; create synonym plan_table FOR system. plan_table;