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

The definition of a constraint

A constraint is a rule or condition imposed on a table. Ensure that the database meets the business rules. Ensure the integrity of the data. When a DML or DDL operation is performed on a table, the system refuses to perform the operation if it causes the data in the table to violate constraints or rules. Constraints can be column-level or table-level. The name of the constraint is not given when defining the constraint, and the ORACE system will automatically generate a name for the constraint in the format SYS_Cn, where n is a natural number. (it is strongly recommended that you define a name for the constraint when creating a table or adding a constraint.)

In Oracle, data integrity can be achieved by using constraints, triggers, and applications (procedures, functions). In these three methods, constraints are easy to maintain and have the best performance, so they are the first choice to maintain data integrity.

Column-level constraints:

Column-level definitions define constraints while defining columns.

Column [CONSTRAINT constraint_name] constraint_type

Table-level constraints:

Table-level definition refers to defining constraints after all columns have been defined. It should be noted here that not null constraints can only be defined at the column level.

Column,...

[CONSTRAINT constraint_name] constraint_type (column,...)

Two-constraint function

Function of constraints: implement some business rules to prevent invalid junk data from entering the database and maintain the integrity of the database (integrity refers to correctness and consistency). This makes the development and maintenance of the database easier.

Classification of three constraints

1 not null (not empty)

If not null is defined on a column, then when inserting data, it must be provided for the column, and the data cannot be NULL. Constraints can only be defined at the column level, not at the table level.

2 unique (unique)

When a unique constraint is defined, the column value cannot be repeated, but it can be null.

3 primary key (primary key)

Data used to uniquely identify table rows that cannot be repeated and cannot be NULL when a primary key constraint is defined. A table can have at most one primary key, but can be constrained by multiple unique.

After you create a primary key or unique constraint, ORACLE automatically creates an index with the same name as the constraint (UNIQUENES is the UNIQUE unique index). It is important to note that there can be only one primary key constraint per table.

4 foreign key (foreign key)

Used to define the relationship between the master table and the slave table. If the foreign key constraint is defined on the slave table, it must have a primary key constraint or unique constraint. When the foreign key constraint is defined, the foreign key column data must exist in the primary key column of the master table or be NULL.

Used to maintain referential integrity between the slave table (Child Table) and the master table (Parent Table). Foreign key constraint is a controversial constraint, on the one hand, it can maintain the data consistency and integrity of the database. Prevent incorrect junk data from entering the database; on the other hand, it will increase the additional overhead of SQL performance such as table inserts and updates, and many systems cancel foreign key constraints through business logic control. For example, in a data warehouse, it is recommended to disable foreign key constraints.

5 check

Used to enforce the conditions that row data must meet, assuming that a check constraint is defined on the sal column and requires the sal column value to be between 1000 and 2000, which will prompt an error if not between 1000 and 2000.

Four-constraint command specification

Constraint names suggest that you define a set of naming rules, otherwise using the system-generated constraint name, it is difficult to associate it with the corresponding tables and fields.

Non-empty 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 instead of applying the NN_ table name _ number. However, depending on the situation, many times the DF_ table name _ column name is named like this, which is often more than 32 characters. So sometimes you need to abbreviate the surface or adopt other rules.

5. Create constraints

1 not null (not empty)

Create

SQL > create table T1 (id number,name varchar2 (20) constraint nn_t1_id not null)

Table created.

SQL > col CONSTRAINT_NAME format A50

SQL > col OWNER format A20

SQL > select constraint_name,constraint_type,owner from user_constraints

CONSTRAINT_NAME C OWNER

SYS_C009047 V SCOTT

FK_DEPTNO R SCOTT

SYS_C009046 C SCOTT

NN_T1_ID C SCOTT

PK_DEPT P SCOTT

PK_EMP P SCOTT

6 rows selected.

Modify

SQL > drop table T1 purge

Table dropped.

SQL > create table T1 (id number,name varchar2 (20))

Table created.

SQL > alter table T1 modify id constraint nn_t1_id not null

Table altered.

SQL > select constraint_name,constraint_type,owner from user_constraints

CONSTRAINT_NAME C OWNER

FK_DEPTNO R SCOTT

SYS_C009046 C SCOTT

NN_T1_ID C SCOTT

PK_DEPT P SCOTT

PK_EMP P SCOTT

SYS_C009047 V SCOTT

2 unique (unique)

Create

SQL > drop table T1 purge

Table dropped.

SQL > create table T1 (id number,qq number,constraint un_t1_qq unique (qq))

Table created.

Modify

SQL > drop table T1 purge

Table dropped.

SQL > create table T1 (id number,qq number)

Table created.

SQL > alter table T1 add constraint un_t1_qq unique (qq)

Table altered.

3 primary key (primary key)

Create

SQL > drop table T1 purge

Table dropped.

SQL > create table T1 (id number,qq number,constraint pk_t1_id primary key (id))

Table created.

Modify

SQL > drop table T1 purge

Table dropped.

SQL > create table T1 (id number,qq number)

Table created.

SQL > alter table T1 add constraint pk_t1_id primary key (id)

Table altered.

4 foreign key (foreign key)

Create

SQL > create table T2 (id number,cc number,constraint fk_t2_id foreign key (id) references T1 (id))

Table created.

Modify

SQL > drop table T2 purge

2

Table dropped.

SQL > create table T2 (id number,cc number)

Table created.

SQL > alter table T2 add constraint pk_t2_id foreign key (id) references T1 (id)

Table altered.

When the external key constraint is defined, the data requiring the external key column must exist in the primary key column (or unique column) of the primary table, or the NULL,FOREING KEY constraint can be defined either at the column level or at the table level.

Keyword description:

(1) FOREING KEY: this option is used to specify that foreign key constraints are defined at the table level. This option must be specified when defining foreign key constraints at the table level, but not at the column level.

(2) REFERENCES: this option is used to specify the primary table name and its primary key column. This option must be specified when defining foreign key constraints.

(3) ON DELETE CASCAED: this option is used to specify cascading deletion options. If this option is specified when defining foreign key constraints, the relevant data from the slave table is cascaded when the master table data is deleted.

(4) ON DELECT SET NULL: this option is used to specify that the external key value related to the transformation is NULL. If this option is specified when the foreign key constraint is defined, the data of the external key column of the slave table will be set to NULL when the master table data is deleted.

5 check (checking constraints)

Create

SQL > create table T3 (id number,sal number,constraint ck_t3_sal check (sal between 5000 and 50000))

Table created.

Modify

SQL > drop table T3 purge

Table dropped.

SQL > create table T3 (id number,sal number)

Six maintenance constraints

1 increase constraints

(1) if you add UNIQUE, PRIMARY KEY, FOREIGN KEY and CKECK, you must use the ADD clause of the ALTER TABLE statement.

(2) if you add NOT NULL constraints, you must use the MODIFY clause of the ALTER TABLE statement, such as:

ALTER TABLE table_name ADD [CONSTRAINT constraint_name]

Constraint_type (column,...)

ALTER TABLE table_name MODIFY column

[CONSTRAINT constraint_name] NOT NULL

2 modify the constraint name

In the same scenario, the constraint name must be unique, and the constraint name cannot have the same name as other objects. When using IMPDP tool or IMP tool to import other objects, if you find an object with the same name, you will make an error.

Syntax:

ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name

TO new_constraint_name

SQL > ALTER TABLE T1 rename constraint pk_t1_id to pk_t1_idnew

2

Table altered.

3. Prohibition and restraint

The prohibition of constraints means that the constraints are temporarily invalidated. When a constraint is prohibited, the constraint rule will no longer take effect. To speed up data loading before using SQL*LOADER or INSERT to load data, you should first disable constraints and then load data.

Syntax:

ALTER TABLE table_name

DISABLE CONSTRAINT constaint_name [CASCAED];-- CASCAED is used to specify the external key of the cascading forbidden slave table

SQL > ALTER TABLE T2 DISABLE constraint pk_t2_id

Table altered.

4 activate constraint

Syntax:

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name

SQL > ALTER TABLE T2 enABLE constraint pk_t2_id

Table altered.

5 Delete constraint

When deleting a primary key constraint for a particular table, if the table has an associated slave table, the primary key constraint must be deleted with the CASCAED option

Syntax:

ALTER TABLE table_name DROP

CONSTRAINT constraint_name | PRIMARY KEY

SQL > ALTER TABLE T3 drop constraint ck_t3_sal

Table altered.

6 display information

1.USER_CONSTRAINTS

2.USER_CONS_COLUMNS

3.SQL > set line 100

SQL > col CONSTRAINT_NAME format A20

SQL > col table_NAME format A20

SQL > col column_NAME format A20

SQL > select constraint_name,table_name,column_name from user_cons_columns where table_name='T1'

CONSTRAINT_NAME TABLE_NAME COLUMN_NAME

PK_T1_ID T1 ID

Table created.

SQL > alter table T3 add constraint ck_t3_sal check (sal > 5000)

Table altered.

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.