Oracle notes: Tables and fields (4)

Oracle notes: Table and field (4) constraints are divided into two types: column constraint and Table constraint. A column constraint is a constraint defined on one column. A Table constraint is a constraint defined on two or more columns. Www.2cto.com 10. primary key constraint: alter table mytable add constraints name primary key (FIELD [, FIELD...]); 11. UNIQUE constraint: alter table mytable add constraints name unique (FIELD); 12. CHECK constraint: alter table mytable add constraints name check (FIELD expression); CHECK constraint is equivalent to validate, which is also divided into column constraint and TABLE constraint, multiple CHECK constraints can be defined for a column. 13. foreign key constraint: alter table mytable add constraints name foreign key field references MYTABLE2 (FIELD) on delete cascade/set null/no action; on delete is divided into three modes: CASCADE: cascade Delete, default; set null: the associated sub-table is SET to NULL, no action: NO operation, this operation will be wrong, so it will be disabled. 14. delete, DISABLE, and activate CONSTRAINTS: delete: alter table mytable drop constraints name; DISABLE: alter table mytable disable constraints name; alter table mytable moidfy constraints name disable; activate: alter table mytable enable constraints name; alter table mytable modify constraints name enable; 15. use the following four constraints: enable validate: Activate the constraint status, check the constraints of existing records, and check the data that will be added later; enable novalidate: Activate Non-validation constraints, do not check the constraints of existing records. You can only check the data added later. disable validate: Disabled Check the validation status. Check the constraints of existing records. ORACLE does not allow adding data. disable novalidate: DISABLE the non-validation status. Do not check the existing records, do not check the data that will be added later. Alter table mytable modify constraints name enable validate /*/*/*. 16. latency constraint: Status: deferrable initially immediate: Check constraints immediately when adding or modifying statements; deferrable initially deferred: do not check statements immediately when adding or modifying statements, check constraints only when committing transactions. Latency constraint, which can be added only when alter table add constraints is set for the first time. The constraint cannot be modified or added after the constraint is set up. However, you can modify the status after the delay is established. For example, create: alter table mytable add constraints name primary key/UNIQUE/* FIELD_NAME deferrable initially immediate; MODIFY: alter table mytable modify constraints name initially deferred; or: set constraint name deferred; 17. view constraints: View constraints in the Table: SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, DEFERRED, DEFERRABLE, status from USER_CONSTRAINTS; you can add WHERE conditions, such as WHERE TABLE_NAME = 'mytable'; view constraints in the column: COLUMN COLUMN_NAME FORMAT A1 0; SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS; you can also add the WHERE condition.