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

Table constraint

Table constraint is a means by which the database can implement business rules and ensure that the data follows the entity-relational model. If

If DML violates the constraint, the entire statement will be rolled back automatically.

1.1 constraint Typ

(1) UNIQUE constraint

Note: for columns with UNIQUE constraints, multiple rows containing NULL can be inserted, but for PRIMARYKEY constraints, this possibility cannot exist.

(2) NOT NULL constraint

(3) PRIMARY KEY constraint

Note: UNIQUE and PRIMARY KEY constraints require indexes, and if they do not exist, they are created automatically. A table has only one primary key.

(4) CHECK constraint

(5) FOREIGN KEY constraint

Note: foreign key constraints are defined on child tables, but UNIQUE or primary key constraints must exist on the parent table at this time. In general, unique constrains all columns and all columns in foreign key constraints preferably also define not null constraints.

[problem] inserting a row with no matching row in the parent table in the word table will cause an error, and deleting the corresponding row will cause an error if the row that already exists in the child table is deleted in the parent table.

[solution 1] when creating a constraint is created as ON DELETE CASCADE.

This means that if you delete rows in the parent table, Oracle searches for index matching rows in the child table and deletes them. This will happen automatically.

[solution 2] (this scenario is more moderate) creates the constraint as ONDELETE SET NULL.

In this case, if you delete a row in the parent table, Oracle searches the child table for index matching rows and sets the foreign key column to empty.

1.2 define constraints

[case] define constraints when creating a table

Create table dept (

2 deptno number (2jin0) not null constraint dept_deptno_pk primary key

3 constraint dept_deptno_ck check (deptno between 10 and 90)

4 dname varchar2 (20) constraint dept_dname_nn not null)

Table created.

SQL > alter tableemp rename to emp1

Table altered.

SQL > create tableemp (

2 empno number (4) not null constraint emp_empno_pk primary key

3 ename varchar2 (20) constraint emp_ename_nn not null

4 mgrnumber (4) constraint emp_mgr_fk references emp (empno)

5 dobdate

6 hiredate date

7 deptno number (2) constraint emp_deptno_fk references dept (deptno)

8 ondelete set null

9 email varchar2 (30) constraint emp_email_uk unique

10 constraint emp_hiredate_ck check (hiredate > = dob + 365mm 16)

11 constraint emp_email_ck

12 check ((instr (email,'@') > 0) and (instr (email,'.') > 0)

Table created.

1.3 constraint statu

At any time, the constraint is enabled or disabled, validated or unvalidated.

ENABLE VALIDATE cannot enter rows that violate the constraint, and all rows in the table conform to the constraint. (ideally, default)

DISABLE NOVALIDATE can enter any data (whether it meets the requirements or not), and there may already be data in the table that does not meet the requirements. (when importing data in bulk)

There may already be data in the ENABLE NOVALIDATE table that does not meet the requirements, but now all the data entered must meet the requirements.

DISABLE VALIDATE, this situation does not exist.

SQL > alter tableemp modify constraint emp_ename_nn disable novalidate

Table altered.

SQL > insert intoemp select * from emp1

SQL > alter tableemp modify constraint emp_ename_nn enable novalidate

Table altered.

SQL > update empset ename = 'NOT KNOWN' where ename is null

SQL > alter tableemp modify constraint emp_ename_nn enable validate

Table altered.

1.4 constraint check

You can check constraints (IMMEDIATE constraints) when executing statements or check constraints (DEFERRED constraints) when committing transactions. By default, all constraints are IMMEDIATE constraints and cannot be delayed.

Create the constraint as a DEFERRED (delay) constraint using the alternative method in the previous example.

SQL > setconstraint emp_ename_nn deferred

SQL > insert intoemp select * from emp1

SQL > update empset ename = 'NOT KNOWN' where ename is null

SQL > commit

SQL > set constraint emp_ename_nn immediate

For a constraint to become a deferred constraint, it must be created using the appropriate method.

SQL > alter table emp add constraint emp_ename_nn

Check (ename is not null) deferrable initiallyimmediate

two

Table altered.

The delay in reexecuting the constraint will be successful at this point.

SQL > setconstraint nn_emp_ename deferred

Constraint set.

SQL > insert intoemp select * from emp1

SQL > update empset ename = 'NOT KNOWN' where ename is null

SQL > commit

SQL > setconstraint nn_emp_ename immediate

Constraint set.

(1) find the name of the constraint

SQL > selectconstraint_name,constraint_type,column_name

2 fromuser_constraints natural join user_cons_columns

3 where table_name ='& Table'

(2) modify the constraint name

Altertable emp rename constraint old_name tonew_name

(3) add the following constraints to the webstore schema

SQL > alter table orders add constraint pk_order_id primary key (order_id)

SQL > alter table products add constraint pk_product_id primarykey (product_id)

SQL > alter table order_items add constraint fk_product_id foreignkey (product_id) references products (product_id)

SQL > alter table order_items add constraint fk_order_id foreign key (order_id) references orders (order_id)

SQL > alter table orders add constraint fk_customer_id foreignkey (customer_id) references customers (customer_id)

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.