Go to Oracle Constraint summary

Https://www.cnblogs.com/kerrycode/archive/2012/05/13/2454614.html

What do you know about Oracle constraints? Is it more vague or quite thorough? If you are familiar with the following questions, congratulations, you have already mastered the constraints better, do not read this article. What is the function of Oracle's constraints? What types of constraints are available (are different versions of Oracle different)? Does the view have constraints? Does the constraint affect SQL performance? What system views, data dictionaries are the constraint information stored in? Can the constraint modify the name? Can I disable a constraint? What are the benefits of delay constraints ...?

Constraint definitions

A constraint is a rule or condition imposed on a table. Ensure that the database meets business rules. Ensure the integrity of the data. When DML or DDL operations are performed on a table, this operation is refused if the operation causes the data in the table to violate the constraints or rules. Constraints can be column-level or table-level. When defining a constraint, the name of the constraint is not given, and the Orace system will automatically generate a name for the constraint in the form SYS_CN, where n is the natural number (it is strongly recommended that you define the name for the constraint when you create the table or increase the constraint.) ).

Constraint function

Constraints: Implement some business rules, prevent invalid garbage data into the database, maintain the integrity of the database (integrity refers to correctness and consistency). This makes it easier to develop and maintain the database.

Constraint classification


Constraints are divided into 6 classes: non-null (NOT NULL) constraints, unique (unique) constraints, primary key (PRIMARY key) constraints, foreign key (FOREIGN key) constraints, condition (CHECK) constraints, ref constraints.

Non-null (NOT NULL) constraint: As the name implies, the column being constrained cannot be a NULL value. Or you'll get an error.

Unique constraint: This column or the values of these columns cannot be the same as defined in each row in the table. Uniqueness must be ensured. Otherwise, it will be illegal to restrict conditions.

Primary KEY (PRIMARY key) constraint: uniquely identifies each row in the table, cannot be duplicated, and cannot be empty. When a primary key or UNIQUE constraint is created, Oracle automatically creates an index with the same name as the constraint (Uniquenes is a unique unique index). It is important to note that each table can have only one primary KEY constraint.

Foreign key (FOREIGN key) constraint: Used to maintain referential integrity between the table (child table) and the primary table (Parent table). FOREIGN KEY constraint is a controversial constraint, it can maintain the data consistency and data integrity of the database on the one hand. Prevent erroneous garbage data from being put into storage, and on the other hand it increases the additional overhead of SQL performance such as table insertions, updates, and many systems that cancel foreign key constraints through business logic control. For example, in a data warehouse, it is recommended to disable foreign key constraints.

Condition CHECK constraint: The constraint is met for each row in the table. Conditional constraints can be defined either at the table level or at the column level. You can define any number of conditional constraints on a column.

The definition of a ref constraint: Ref column by definition references an object in another object type or in a relational table. A ref constraint lets you further describe the relationship between the REF column and the object it references.

The types of constraints for ORACLE 11 databases are as follows:

The types of constraints for ORACLE 10 databases are as follows:

Under Oracle's Official document Http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm

C (check constraint on a table)
P (primary key)
U (Unique key)
R (referential integrity)
V (with CHECK option, on a view)
O (with Read only, on a view)

Http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1044.htm#REFRN20047

C Check constraint on a table
P Primary Key
U Unique Key
R Referential integrity
V with CHECK option, on a view
O with Read only, on a view
H Hash Expression
F Constraint that involves a REF column
S Supplemental Logging

For H, S type, it is not clear, and from the above I found in the 11g database experiment results, also did not find these two types. (Friends who have an understanding can tell one or two)

Constraint naming conventions

Constraint name It is recommended to define a set of naming rules, otherwise it is very difficult to associate a system-generated constraint name with a corresponding table or field.

Non-null constraint nn_ table name _ Column name
Unique constraint Uk_ table name _ Column name
PRIMARY KEY constraint Pk_ table name
FOREIGN KEY constraint Fk_ table name _ Column name
Conditional constraint Ck_ Table name _ Column name
Default constraint df_ table name _ Column name

If the constraint name exceeds the 32-bit length, it is recommended that you abbreviate the table name without applying the Nn_ table name _ number. However, depending on the circumstances, many times the Df_ table name _ column name is named, often beyond the 32 characters. So sometimes you need to abbreviate the surface or use other rules.

Constraint actions

Constraint management

Create various constraints

First look at how to create a PRIMARY KEY constraint

CREATE TABLE "DM". " DEPT "(                   " DEPTNO "number     (2,0) PRIMARY KEY,                " Dname "      VARCHAR2 (+),                " LOC "        VARCHAR2 ()   Tablespace "Tbs_dm_dat";  COMMENT on TABLE "DM". " DEPT "is ' Department table ';                                                                                 COMMENT on COLUMN "DM". " DEPT "." DEPTNO "        is  ' Department number ';                                                                 COMMENT on COLUMN "DM". " DEPT "." Dname "         is  ' department name ';                                                                 COMMENT on Cloumn "DM". " DEPT "." LOC "is"  department where ';

At this point, its constraint name is created by the system. ：

If we create it in the following script, the constraint looks like this

1 CREATE TABLE "DM". " DEPT "  2 (     3                " DEPTNO "number         (2,0),  4                " dname "         VARCHAR2",  5                "LOC"             VAR CHAR2,  6                CONSTRAINT "pk_dept" PRIMARY KEY ("DEPTNO") USING INDEX tablespace tbs_dm_in 7)   Tablespac E "Tbs_dm_dat"; 8   9   COMMENT on   TABLE "DM". " DEPT "is ' Department table '; COMMENT on COLUMN" DM "." DEPT "." DEPTNO "        is  ' Department number ';" COMMENT on COLUMN "DM". DEPT "." Dname "            is  ' department name ';" COMMENT on Cloumn "DM". DEPT "." LOC "is"  department where '; 19   

If the constraint type of a table is more, you will see a bunch of sys_cn such constraints, if you do not carefully check the following fields, it is difficult to look at the constraint type, constraint corresponding field.

Limitations of PRIMARY KEY constraints

The original document is as follows: (translation below)
Restrictions on Primary Key Constraints Primary Constraints is subject to the following restrictions:

A table or view can has only one primary key.
A table or view has and has only one primary key

None of the columns in the primary key can is LOB, long, long RAW, Varray, NESTED TABLE, BFILE, REF, TIMESTAMP with time Z One, or user-defined type. However, the primary key can contain a column of TIMESTAMP with LOCAL time ZONE.
Primary key fields cannot be lob, log, log RAW, Varray, NESTED TABLE, BFILE, REF, TIMESTAMP with time zone, or user-defined type. However, it can contain fields of the timestamp with LOCAL time zone type.

The size of the primary key cannot exceed approximately one database block.
The primary key size cannot exceed one block size.

A Composite PRIMARY key cannot has more than columns.
The primary key combination key cannot exceed 32 columns.

You cannot designate the same column or combination of columns as both a primary key and a unique key.
You cannot specify that a column or a combined column is both a primary key and a unique key.

You cannot specify a primary key is creating a subview in an inheritance hierarchy. The primary key can is specified only for the top-level (root) view.
When you create a child view in an inheritance hierarchy, you cannot specify a primary key. The primary key can uniquely specify the top-level (root) view.

Go to Oracle Constraint summary