Shulou(Shulou.com)05/31 Report--

This article mainly introduces the database constraints of validate and able how to use, the article is very detailed, has a certain reference value, interested friends must read it!

Create a uniqueness constraint and automatically create the same uniqueness index as the constraint name

Create a check constraint without generating an index

Create a foreign key constraint without generating an index

Enable constraints:

Enable (validate): enable constraints, create indexes, and enforce constraints on existing and newly added data.

Enable novalidate: enable constraints, create indexes, and enforce constraints only on newly added data, regardless of existing data in the table.

Disable constraints:

Disable (novalidate): close the constraint, delete the index, and modify the data of the constraint column.

Disable validate: close constraints, delete indexes, and cannot insert / update / delete tables.

Where enable\ validate is used.

1. When the materialized view is to realize query rewriting, the state of the primary foreign key constraint of the associated table must be guaranteed to be enable and validate.

2. To achieve star conversion, we must ensure that the state of the foreign key constraint of the fact table, that is, the field of the main table, is enable and validate.

SQL > alter table SALES disable novalidate constraint SALES_CHANNEL_FK

Table altered.

SQL > select constraint_name,constraint_type,status,validated from user_constraints where table_name='SALES' and constraint_type='R' and CONSTRAINT_NAME='SALES_CHANNEL_FK'

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED

--

SALES_CHANNEL_FK R DISABLED NOT VALIDATED

SQL > alter table SALES disable validate constraint SALES_CHANNEL_FK

Table altered.

SQL > select constraint_name,constraint_type,status,validated from user_constraints where table_name='SALES' and constraint_type='R' and CONSTRAINT_NAME='SALES_CHANNEL_FK'

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED

--

SALES_CHANNEL_FK R DISABLED VALIDATED

SQL > alter table SALES enable novalidate constraint SALES_CHANNEL_FK

Table altered.

SQL > select constraint_name,constraint_type,status,validated from user_constraints where table_name='SALES' and constraint_type='R' and CONSTRAINT_NAME='SALES_CHANNEL_FK'

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED

--

SALES_CHANNEL_FK R ENABLED NOT VALIDATED

SQL > alter table SALES enable validate constraint SALES_CHANNEL_FK

Table altered.

SQL > select constraint_name,constraint_type,status,validated from user_constraints where table_name='SALES' and constraint_type='R' and CONSTRAINT_NAME='SALES_CHANNEL_FK'

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED

--

SALES_CHANNEL_FK R ENABLED VALIDATED

The above is all the contents of the article "how to use validate and able with constraints in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.