Oracle constraint classification, oracle constraint classification

Oracle constraint classification, oracle constraint classification
Database Integrity is the correctness and compatibility of the data in the database. It is used to prevent users from adding invalid semantics to the database. Database integrity is guaranteed by various integrity constraints. It can be said that database integrity design is the design of database integrity constraints. Table integrity constraints can be divided into different types from different perspectives. You can select two angles: the scope of the constraint and the purpose of the constraint.

Table integrity constraints can be divided into the following two categories based on the constraints:
Table-level constraints: these constraints apply to tables and apply to multiple columns in a table.
Column-level constraints: Apply to a column in a table and only apply to this column.

Table integrity constraints can be divided into five categories according to the purpose of the constraints:

Brief description of constraints
Not null c non-null constraint (a specified column does not allow null values to be stored)
Primary key p primary key constraint (a primary key consists of one or more columns and uniquely identifies one column in the table)
Unique u unique constraint (unique constraint, specifying a column or a group of columns can only store unique values)
Check c check constraints (unique values must be stored for values in a specified column or group of columns)
Foreing key r foreign key constraint (the foreign key references a column in another table in the table)

View user_constraints
You can query the data dictionary view user_constraints to learn the basic information about all constraints in the current user mode.
Column type description
Owner varchar2 (30) Constraint owner
Constraint_name varchar2 (30) constraint name
Constraint_type varchar2 (1) Constraint type
Table name for the table_name varchar2 (30) Constraint
Status varchar2 (8) Constraint status (enabled or disabled)
Deferable varchar2 (14) whether the constraint can be delayed
Deferrd varchar2 (9) immediate execution or delayed execution

You can view the columns that define constraints by querying the Village Data Dictionary view user_cons_columns.
Column type description
Owner varchar2 (30) Constraint owner
Constraint_name varchar2 (30) constraint name
Table name for the table_name varchar2 (30) Constraint
Column_name varchar2 (4000) Constraints
The specific operations are as follows:
SQL> column owner format a6
SQL> column constraint_name format a15
SQL> column table_name format a10
SQL> column column_name format a10
SQL> select owner, constraint_name, table_name, column_name // Constraints
2 from user_cons_columns
3 where table_name = 'T ';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM
-----------------------------------------
SYSTEM SYS_C0010065 t tid constraint name column
SYSTEM SYS_C0010064 t tid constraint name column
SQL>

SQL> select constraint_name, constraint_type // Constraints
2 from user_constraints
3 where table_name = 'T ';
CONSTRAINT_NAME C
----------------
SYS_C0010065 P primary key constraint

SYS_C0010064 C non-empty Constraint

The above content comes from the Learning Record.