Shulou(Shulou.com)06/01 Report--

I. the concept of constraint

Generally speaking, constraint is a means of development and design in order to make the filled data more in line with business norms and ensure data integrity.

If the DML (update, insert, delete row) or DDL (in this case, ALTER change operation) operation does not comply with the business specification (constraint) requirements, the database will reject the request

Constraints can be either table-level or column-level, and it is important to emphasize that notnull must be column-level.

When defining a constraint, it should normally be defined according to the syntax format and naming convention. If the name of the constraint is not given, Oracle will give the constraint according to SYS_Cn (n is a natural number, generated by Oracle based on pipelining), such as SYS_C001684. It is recommended that you should develop good learning habits and name the constraint correctly.

II. Classification of Oracle constraints

Non-empty (NOT NULL) constraint, unique (UNIQUE) constraint, primary key (PRIMARY KEY) constraint, foreign key (FOREIGN KEY) constraint, conditional (CHECK) constraint, REF constraint.

3. Oracle constraint relation comparison diagram

Constraint name

Chinese meaning

Constraint

Types

Naming convention

Features

Remarks

NOT NULL

Non-empty constraint

C

NN_ table name _ column name

Null values are not allowed

Can only be defined on a column

UNIQUE

Unique value constraint

U

UK_ table name _ column name

Values are not allowed to repeat, but can be NULL and NULL can have more than one, because NULLNULL, the number of combined columns cannot exceed 32.

The column that needs to define UNIQUE cannot be a primary key column, that is, a column cannot have both primary key and UNIQUE constraints

PRIMARY KEY

Primary key constraint

P

PK_ table name

The value cannot be repeated and cannot be NULL, and the number of combined columns cannot exceed 32.

A table or view can have only one primary key

FOREIGN KEY

Foreign key constraint

R

FK_ table name _ column name

The value of the primary key of the stored parent table, which is dependent on the parent table. When inserting a new value, it cannot be inserted if the value of the foreign key is not in the parent table.

Foreign key is a double-edged sword, while adding child parent table relationship verification, at the same time at the expense of oracle data performance, now large data design will avoid using foreign keys.

CHECK

Conditional constraint

C

CK_ table name _ column name

Custom constraint checks are defined according to the requirements of the developer and designer, such as checking that the input values meet a certain range, such as the age column needs to be greater than or equal to 1 year old and less than Deng Yu 120 years old CHECK (AGE BETWEEN 1 AND 120).

Flexible definition of constraints

IV. Constraint viewing

1. DBA_CONSTRAINTS: all constraint definitions in the database

2. ALL_CONSTRAINTS: constraints on tables that the user has permission to view

3. USER_CONSTRAINTS: belongs to the constraint created by the current login user

Description of key fields in constraint view

OWNER: constraint creator

CONSTRAINT_NAME: the name of the constraint

CONSTRAINT_TYPE: type of constraint, refer to the figure above

SEARCH_CONDITION: you can check the specific content of the constraint by clicking on the large text.

STATUS: constrains the current state, whether it is valid or not

Fifth, constraint creation

1. Under normal circumstances, the constraint should be fully defined when the table is designed and created. If you find that you need to define a constraint in use, you need to first deal with the data that does not meet the requirements of the specification and then define the constraint as soon as possible.

2. Below, we will create constraints when using CREATETABLE, including NOT NULL,PRIMARY KEY,FOREIGN KEY,UNIQUE and CHECK constraints.

SQL > CREATETABLEconstraint_test (

Cno VARCHAR2 (4) PRIMARYKEY

Cname VARCHAR2 (40) UNIQUE

Cdate DATE DEFAULTTO_DATE (TO_CHAR (SYSDATE,'YYYY-MM-DD'), 'YYYY-MM-DD') NOTNULL)

3. The following is the way to append constraints using ALTER.

Add a note column and require you to use the CHECK constraint to check for strings that can only be entered with letters

SQL > ALTERTABLE constraint_test ADD note VARCHAR2

SQL > ALTERTABLE constraint_test ADD CONSTRAINT ck_constraint_test_note

CHECK (REPLACE (TRANSLATE (UPPER (note), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','A'),') IS NOT NULL)

VI. Constraint deletion

1. Syntax format: ALTERTABLE [table name] DROPCONSTRAINT [constraint name]

2. Example: ALTERTABLE constraint_test DROPCONSTRAINTck_constraint_test_note

VII. Constraint renaming

1. Syntax format:

ALTERTABLE [Table name] RENAME CONSTRAINT [Old constraint name] TO [Old Testament name]

2. Example: ALTERTABLE constraint_test RENAMECONSTRAINTnn_constraint_test_note TO ck_constraint_test_note

VIII. Disable constraints

1. Syntax format:

ALTERTABLE [table name] DISABLECONSTRAINT [constraint name] CASCADE

2. For example:

ALTERTABLE constraint_test DISABLECONSTRAINTck_constraint_test_note [CASCADE]

3. 1:cascade is an optional option. For example, the primary key in the parent table is the foreign key of the child table. If we want to disable the primary key constraint effect in the parent table as well as the foreign key constraint in the word table, we can enable the parameter CASCADE.

4. Note 2: if disabling a constraint will automatically create an index by Oracle (such as primary key, foreign key, unique, etc.), while disabling the constraint, it will also delete the index corresponding to the constraint, which obviously makes us do not want to see it for big data tables, so I can choose to keep the index when disabling the constraint by:

ALTERTABLE [table name] DISABLECONSTRAINT [constraint name] INDEX

IX. Enable constraints

1. Syntax format:

ALTERTABLE [table name] ENABLECONSTRAINT [constraint name] INDEX

2. Note: when we disable primary and foreign keys, we can be lazy by disabling the primary key of the parent table followed by cascade, but if we re-enable it, we need to enable it separately.

10. Constraint state

ENABLE (available), VALID (valid), INVALID (invalid), DISABLE (disabled)

Suggestions on the use of constraints

1. You should define the various Oracle constraints you need when you create the table. If not, add the required Oracle constraints as early as possible, which will make the system more reliable and easier to maintain; (depending on the circumstances)

2. Excessive constraint checking in Oracle system will greatly reduce the efficiency of Oracle database system.

XII, XII, other chapters on restraint

1. The field cannot be specified as NULL or NOT NULL on the view.

2. When deleting reports that use primary or foreign keys, you must delete the word table first and then delete the parent table, or add the cascade keyword after the parent table deletion statement. Step-by-step operation is recommended.

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.