Constraints on the 1z0-051-ddl-table

Constraints on tables

Table constraints are a means by which a database can enforce business rules and ensure that data follows an entity-relational model. If

DML violates the constraint, the entire statement is automatically rolled back.

1.1 Constraint Type

( 1 ) UNIQUE Constraints

Note: For a UNIQUE constrained columns, you can insert more than one containing NULL the row, and for PRIMARYKEY constraint, this possibility cannot exist.

( 2 ) Not NULL Constraints

( 3 ) PRIMARY KEY Constraints

Note: UNIQUE and the PRIMARY KEY the constraint requires an index and, if it does not exist, it is created automatically. A table has only one primary key.

( 4 ) CHECK Constraints

( 5 ) FOREIGN KEY Constraints

Note: A FOREIGN KEY constraint is defined on a child table, but at this point a unique or PRIMARY KEY constraint must exist on the parent table. In general, it is best to define a NOT NULL constraint for all columns in a unique constraint and for all columns in the FOREIGN KEY constraint.

Problem inserts a row in the parent table that does not have a matching row in the Word table, an error occurs, and a row that already exists in the child table is deleted in the parent table, and the corresponding row is dropped and an error is thrown.

"Solution 1" creates a constraint that is created as on DELETE CASCADE.

This means that if you delete rows from the parent table, Oracle will search the child tables for the index matching rows and delete them. This will happen automatically.

Solution 2 (this scenario is milder) creates the constraint as OnDelete SET NULL.

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

1.2 Defining Constraints

Case defines constraints when creating a table

CREATE TABLE Dept (

2 deptno Number (2,0) NOT NULL constraint DEPT_DEPTNO_PK primary KEY,

3 constraint dept_deptno_ck Check (Deptno between and 90),

4 dname varchar2 () constraint dept_dname_nn not NULL);

Table created.

sql> alter tableemp rename to EMP1;

Table altered.

Sql> Create Tableemp (

2 empno Number (4,0) NOT NULL constraint EMP_EMPNO_PK primary KEY,

3 ename varchar2 () constraint emp_ename_nn not NULL,

4 Mgrnumber (4,0) constraint EMP_MGR_FK references emp (empno),

5 Dobdate,

6 HireDate Date,

7 deptno number (2,0) constraint EMP_DEPTNO_FK references dept (DEPTNO)

8 OnDelete Set NULL,

9 Email varchar2 (+) constraint Emp_email_uk unique,

Constraint emp_hiredate_ck Check (hiredate >=dob +365*16),

Constraint Emp_email_ck

Check (instr (email, ' @ ') > 0) and (InStr (email, '. ') > 0));

Table created.

1.3 Constraint State

• enable     validate  

• disable     novalidate  can enter any data (whether or not it meets the requirements) , there may already be data in the table that does not meet the requirements.

• enable  novalidate  

•  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 checking

You can either check the constraint (IMMEDIATE constraint) on the execution statement or commit the transaction as a CHECK constraint (deferred constraint). By default, all constraints are immediate constraints and cannot be deferred.

The substitution method in the previous example creates the constraint as a deferred (delay) constraint.

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;

To make a constraint a delay constraint, you must create it using the appropriate method.

Sql>alter table EMP Add constraint Emp_ename_nn

Check (ename is not null) deferrable initiallyimmediate;

2

Table altered.

The constraint delay is re-enforced at this point to succeed.

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 ) to webstore the following constraints are added to the 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);

This article is from the "struggle more than" blog, please be sure to keep this source http://peenboo.blog.51cto.com/2865551/1793884

Constraints on the 1z0-051-ddl-table