Integrity constraint rules for Oracle databases

oracle| Data | database
Integrity constraints are used to enhance data integrity, Oracle provides 5 kinds of integrity constraints:

Check
Not NULL
Unique
Primary
Foreign Key

Integrity constraints are a rule that does not occupy any database space. The integrity constraint exists in the data dictionary and is used during execution of SQL or Pl/sql. The user can indicate whether the constraint is enabled or disabled, and when the constraint is enabled, he enhances the integrity of the data, otherwise, but the constraint always exists in the data dictionary.

Disable constraint, using ALTER statement

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
Or

ALTER TABLE Policies DISABLE CONSTRAINT Chk_gender
If you want 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;
Check constraint

A check constraint on a data column requires a special Boolean condition or sets the data column to True, and the value of at least one data column is a simple business rule that null,check constraints use to enhance the content of the data in the table. Users use CHECK constraints to ensure consistency of data rules. A check constraint can involve other data columns with a CHECK constraint, but not other rows or tables, or call function sysdate,uid,user,userenv. If the user's business rules require this kind of data checking, you can use triggers. Check constraints do not protect data columns and objects, nested tables, varry, ref, and so on for LOB data types. A single data column can have more than one check constraint protection, and a check constraint can protect multiple data columns.

The check constraint that creates the table uses the CREATE TABLE statement to change the constraint of the table using the ALTER TABLE statement.

Grammar:

CONSTRAINT [constraint_name] CHECK (condition);
Check constraints can be created or added to a table constraint, and table constraint syntax must be used when a check constraint protects multiple data columns. The constraint name is optional and if the name does not exist, then Oracle will produce a unique name starting with Sys_.

Cases:

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

The NOT NULL constraint is applied to a single data column, and the data columns that he protects must have data values. By default, Oracle allows any column to have null values. Some business rules require that a data column must have a value, and a NOT NULL constraint ensures that all data rows for that column have a value.

Cases:

CREATE TABLE Policies
(policy_id number,
Holder_name VARCHAR2 () not NULL,
Gender VARCHAR2 (1),
Marital_status VARCHAR2 (1),
Date_of_birth DATE not NULL
);
ALTER TABLE statements for NOT NULL are somewhat different from other constraints.

ALTER TABLE Policies MODIFY Holder_name not NULL
Uniqueness constraint (UNIQUE constraint)

A Uniqueness constraint protects multiple columns of data in a table, guaranteeing that data in any two rows in a protected data column is not the same. Uniqueness constraints are created with tables and can be modified using the ALTER TABLE statement after the uniqueness constraint is created.

Grammar:

column_name data_type CONSTRAINT constraint_name UNIQUE
If a Uniqueness constraint protects multiple data columns, the uniqueness constraint is incremented as a table constraint. The syntax is as follows:

CONSTRAINT constraint_name (column) UNIQUE USING INDEX tablespace (tablespace_name) STORAGE (stored clause)
Uniqueness constraints are enhanced by a B-tree index, so you can use special features, such as table spaces or storage parameters, for indexes in the using substring. The CREATE TABLE statement establishes a unique index for the target data column while creating 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)
);
The user can disable a sexual constraint but he still exists, disabling a uniqueness constraint using the ALTER TABLE statement

ALTER TABLE Insured_autos DISABLE constrain unique_name;
Remove uniqueness constraint, use alter TABLE .... DROP Constrain statement

ALTER TABLE Insured_autos DROP constrain unique_name;
Note The user cannot delete a Uniqueness constraint on a table that has a foreign key pointing to it. In this case, the user must first disable or remove the foreign key (foreign key).

Deleting or disabling a uniqueness constraint typically deletes the associated unique index, thereby reducing database performance. Frequent deletion or disabling of uniqueness constraints can result in performance errors caused by missing indexes. To avoid this error, you can take the following steps:

1. Create a non-unique index on a data column that is protected by a uniqueness constraint.

2. Add Uniqueness Constraint

Primary KEY (Primary key) constraint

Table has a unique PRIMARY KEY constraint. The primary key of a table can protect one or more columns, and a PRIMARY key constraint can work with a NOT NULL constraint on each data column. A combination of a NOT NULL constraint and a uniqueness constraint guarantees that the primary key uniquely identifies each row. Like uniqueness constraints, primary keys are enhanced by the B-tree index.

Create a PRIMARY KEY constraint use a CREATE TABLE statement to create with a table, and you can use the ALTER TABLE statement if the table has already been created.

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
);
As with uniqueness constraints, if a 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 Index
STORAGE (INITIAL 1M NEXT 10M pctincrease 0)
);
Disabling or removing a 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 (Foreign key constraint)

A FOREIGN KEY constraint protects one or more data columns, guaranteeing that the data for each data row contains one or more null values, or that a primary key or uniqueness constraint is also on the protected data column. A reference (primary KEY or uniqueness constraint) constraint can protect the same table, or it can protect different tables. Unlike primary keys and uniqueness constraints, different foreign keys do not implicitly establish an B-tree index. When dealing with a foreign key, we often use the term parent table (parent table) and the child table, the parent table representing the table referenced by the primary KEY or uniqueness constraint, and the child table representing the table that references the primary key and uniqueness constraint.

Create a foreign key using the CREATE TABLE statement, and if the table is already established, use the ALTER TABLE statement.

CREATE TABLE Insured_autos
(policy_id number CONSTRAINT POLICY_FK
REFERENCE Policies (policy_id
On 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 what the child records do if the parent record is deleted. By default, deleting a parent record is prohibited if the child record is still present.

Foreign keys and Null values

Processing of NULL values in a data column protected by a FOREIGN key constraint can have unpredictable results. ORACLE enhances the FOREIGN KEY constraint using the ISO standar Match none rule. This rule stipulates that if any foreign key action data column contains a null value, then any data column that retains the key does not have a matching value in the parent table.

For example, in the parent table automobiles, the primary key is used on the data column make,model,year, the user uses the table insured_autos has an external constraint pointing to the aotomobiles, noting that in the Insures_ The model column of a data row in autos is a null value that has been checked by a constraint, even if the Make column does not appear in the parent table automobiles, as in the following table:

Table 1 Automobiles

Make MODEL Yearford Taurus2000toyotacamry 1999
Table 2 Insured_autos

Policy_idmake MODEL YEAR576 Ford Taurus 2000577 Toyotacamry 1999 578 Tucker NULL 1949
Delay constraint test (Deferred Constraint Checking)

The constraint test is divided into two cases, one is to check whether the data satisfies the constraint condition after each statement is finished, this kind of test is called the immediate constraint test (immediately checking), the other is to test the data after the transaction is finished called the delay constraint test. By default, Oracle constraint checking is an immediate test (immediately checking), and if the constraint is not met, an error message is first, but the user can select the delay constraint test through the SET constraint statement. The syntax is as follows:

SET CONSTRAINT constraint_name| All defeerred| IMMEDIATE--;
Sequence (sequences)

The Oracle sequence is a continuous number generator. Sequences are often used for artificial keywords, or to sort data rows otherwise the data rows are unordered. Like constraints, a sequence exists only in the data dictionary. The serial number can be set to rise, drop, and can be used without restriction or reuse until a limit value. The creation sequence uses the SET sequence statement.

CREATE SEQUENCE [schema] SEQUENCE KEYWORD
Keyword includes the following values:


KEYWORD describes the first number generated by the start with definition sequence, which defaults to 1INCREMENT by defining whether the serial number is ascending or descending, for a descending sequence increment by a negative value minvalue define the minimum values that the sequence can generate. This is the limit value in the descending sequence. By default, this value is Nominvalue,nominvalue, and for ascending 1, the maximum number that can be generated for descending order is the -10e26.maxvalue sequence. This is the limit value in the ascending sequence, the default maxvalue is Nomaxvalue,nomaxvalue, for ascending 10E26, and 1 for descending. CYCLE set the sequence value can be repeated after the limit value is reached nocycle the set sequence value cannot be duplicated after the limit value is reached, which is the default setting. When attempting to produce a maxvalue+1 value, an exception cache defines the size of the memory block that the sequence value occupies, and the default value is 20NOCACHE to force the data dictionary update each time the serial number is generated, ensuring that there is no interval between the sequence values when the sequence is created, the START The with value must be equal to or greater than minvalue.
Delete a sequence using the drop sequence statement

DROP SEQUENCE Sequence_name

Index (INDEXES)

An index is a data structure that improves query performance, and in this section we discuss how the index can improve query performance. Oracle provides the following types of indexes:

Index types such as B-tree, hash (hash), Bitmap (bitmap)
Index based on the original table
Function-based indexing
field (domain) index

The actual application is mainly B-tree index and bitmap index, so we will focus on these two types of indexes.

B-tree Index

The B-tree index is the most common index, and the index established by default is this type of index. B-tree indexes can be unique or not unique, can be single (based on one column) or connected (multiple columns). The B-tree index provides the best performance when retrieving a high cardinality data column (where a high cardinality data column refers to a number of different values for that column). This provides a more efficient way to remove a smaller data b-tree index than full table retrieval. However, when the scope of the inspection exceeds 10% of the table, the performance of retrieving the data cannot be improved. As the name implies, the B-tree index is based on a two-dollar Tree, consisting of a branch block (branch blocks) and a leaf block (leaf blocks) that contains the index column (the keyword) and the address of another index. The leaf block contains keywords and rowid for each matching row in the table.

Bitmap index

Bitmap indexes are used primarily for decision support systems or static data, and row-level locking is not supported. Bitmap indexes can be simple (single-column) or connected (multiple columns), but most of them are simple in practice. Bitmap indexes are best used for low to medium cluster (cardinality) columns on which multiple bitmap indexes can be used in conjunction with and OR operators. Bitmap indexes use bitmaps as key values, and each data row bitmap in a table contains True (1), FALSE (0), or null values. Bitmap indexes are stored in the page nodes of the b-tree structure. The B-tree structure makes finding bitmaps very convenient and fast. In addition, bitmaps are stored in a compressed format, thus taking up much less disk space than B-tree indexes.

Synonyms (synonyms)

A synonym is an alias for another data object. Public synonyms are for all users, whereas private synonyms are relative only to the object owner or to the account to which the permission is granted. Synonyms in the local database can represent data objects such as tables, views, sequences, programs, functions, or packages, or they can represent objects in another database.

The syntax for creating synonyms is as follows:

CREATE [public] synonym synonym_name for [schema.] object[@db_link];
Cases:

CREATE public synonym policies for poladm.policies@prod;

CREATE synonym plan_table for system.plan_table;