Detailed description of Oracle database integrity constraints

Restrictions: constraints are used to ensure that database data meets specific business logic or enterprise rules. If constraints are defined and the data does not comply with the constraints, therefore, DML operations (INSERT, UPDATE, and DELETE) cannot be successfully executed. 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 that 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. * If a constraint only applies to individual fields, you can define the constraint at the field level or at the table level. However, if a constraint acts on multiple fields, you must define constraints at the table level * when defining constraints, you can use the CONSTRAINT keyword to name the constraints. If not specified, ORACLE automatically creates default name-defined CONSTRAINT column-level constraints for the constraints: column [CONSTRAINT constraint_name] constraint_type table-level constraints: column ,..., [CONSTRAINT constraint_name] constraint_type (column ,...) check constraint * the expression of the CHECK constraint must reference one or more fields in the table, the calculation result of the expression must be a Boolean value * can be defined at the table or field level * multiple CHECK constraints can be defined for the same field, you can also define the not null constraint create table emp06 (eno I NT, name VARCHAR2 (10), salary NUMBER (6, 2), CHECK (salary BETWEEN 1000 AND 5000); not null constraints can only define not null constraints at the field level, multiple not null constraints can be defined in the same table. The not null constraint is applied to a single data column, and the data column protected by the constraint must have data values. 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. Create table emp01 (eno int not null, name VARCHAR2 (10) CONSTRAINT nn_name2 not null, salary NUMBER (6, 2); unique constraints define fields with UNIQUE constraints that cannot contain duplicate values, UNIQUE constraints can be defined for one or more fields. Therefore, UNIQUE can be defined at the field level or at the table level. Fields with UNIQUED constraints can contain null values. create table emp02 (eno int unique, name VARCHAR2 (10) CONSTRAINT u_name UNIQUE, salary NUMBER (6, 2 )); the primary key constraint ORACLE automatically creates a unique index and a not null constraint for fields with the primary key constraint (PRIMARY code segment). When defining the primary KEY constraint, it can be its index, a table can have at most one primary key constraint create table depto04 (dno int primary key, dname VARCHAR2 (10), loc VARCHAR2 (20 )); foreign key constraint * fields defined as foreign key constraints can only contain the value or NULL value of the reference code segment in the corresponding other tables * can be defined as a combination of one or more fields FOREIGN KEY constraint * defines the external code segment of the foreign key constraint and the corresponding reference code segment can exist in the same table, in this case, it is called "Self-reference" * for the same field, you can define both the foreign key constraint and the not null constraint. The field that defines the foreign key constraint is called the "external code segment ", fields referenced by the forgien key constraint are referred to as "reference code segments". The reference code must be the primary code or unique code. tables that contain external codes are called subtables, A table containing a reference code is called a parent table. create table emp04 (eno INT, name VARCHAR2 (10), salary NUMBER (6, 2), dno int constraint fk_dno REFERENCES dept04 (dno ));