Oracle Study Notes 2-constraint addition and Deletion

1. Oracle Constraints
· If a constraint only applies to individual fields, you can define the constraint at the field level or at the table level. However, if a constraint acts on multiple fields, constraints must be defined at the table level

· When defining constraints, you can use the constraint keyword to name constraints. If not specified, Oracle will automatically create a default name for the constraints.

2. Define the primary key constraint (single field)
SQL> Create Table bkeep1 (empno number (5) primary key );

Table created.

3. Specify the constraint name.
SQL> Create Table bkeep2 (empno number (5) Constraint emp_pk primary key );

Table created.

4. Define the primary key constraint (multiple fields are defined at the table level)
SQL> Create Table bkeep3

2 (empno number (5), deptno number (3) not null,

3 constraint emp_pk primary key (empno, deptno ));

Constraint emp_pk primary key (empno, deptno ))

*

Error at line 3:

ORA-02264: name already used by an existing constraint // The constraint name must be unique

 

SQL> RUN

1 create table bkeep3

2 (empno number (5), deptno number (3) not null,

3 * constraint bkeep3_pk primary key (empno, deptno ))

Table created.

 

5. [important] Oracle automatically creates a unique index and a not null constraint for fields with the primary key constraint.
6. Add constraints by modifying the table
SQL> ALTER TABLE bkeep4 add constraint bkeep4_pk primary key (employee_id );

Table created.

SQL> ALTER TABLE employees add constraint emp_pk primary key (empno, deptno)

 

7. Not null constraint (the not null constraint can only be defined at the field level. Multiple not null constraints can be defined in the same table)
SQL> ALTER TABLE employees modify deptno not null/null // syntax
SQL> ALTER TABLE bkeep5 modify department_id not null;

Alter table bkeep5 modify department_id not null

*

Error at line 1:

ORA-02296: cannot enable (zbb.)-null values found // If the table already has null, it cannot be changed to not null Constraints

 

SQL> ALTER TABLE bkeep5 modify email NULL;

Table altered.

 

8. unique constraints
SQL> Create Table bkeep6

2 (empno number (5), ename varchar2 (25 ),

3 phone varchar2 (15), email varchar2 (15) unique,

4 deptno number (3) not null,

5 constraint bkeep6_ename_phone_uk unique (ename, phone ));

Table created.

Or
SQL> ALTER TABLE bkeep6

Add constraint bkeep6_ename_phone_uk unique (ename, phone)

Using index tablespace users

 

Fields defining the unique constraint cannot contain duplicate values. You can define a unique constraint for one or more fields. Therefore, unique can be defined at the field or table level,

TIPS: fields with the uniqued constraint can contain null values.

 

9. Foreign key constraint
· Fields defined as the foreign key constraint can only contain values or null values that reference the code segment in other tables.

· You can define the foreign key constraint for a combination of one or more fields.

· The external code segment defining the foreign key constraint and the corresponding reference code segment can exist in the same table. This situation is called "Self-reference"

· The foreign key constraint and not null constraint can be defined for the same field at the same time.

 

[Concept] a field that defines the foreign key constraint is called an "external code segment". The field referenced by the forgien key constraint is called a "reference code segment ", the reference code must be a primary or unique code. A table containing external code is called a sub-table, and a table containing the reference code is called a parent table.

[Vernacular] define the foreign key in the child table to reference the column value in the parent table. The column in the parent table must be the primary or unique code!

A:

Create Table employees

(.....,

Deptno number (3) not null,

Constraint emp_deptno_fk foreign key (deptno)

References dept (deptno)

)

 

If the external code in the sub-table has the same name as the reference code in the master table, you can write it as follows:

B:

Create Table employees

(.....,

Deptno number (3) not null

Constraint emp_deptno_fk references Dept

)

 

Note:

In the above example (B), not null is followed by no comma, because the contraint of this sentence is followed by the deptno column and belongs to the column definition, so you do not need to specify the column. In Example A, the table definition is used and the column must be specified. Therefore, you must add a comma (,). You cannot define the column after it. You can also write it as follows:

 

Create Table employees

(Empno char (4 ),

Deptno char (2) not null constraint emp_deptno_fk references Dept,

Ename varchar2 (10)

)

The table definition contraint can only be written at the end. Let's look at two examples:

Create Table employees

(Empno number (5 ),

Ename varchar2 (10 ),

Deptno char (2) not null constraint emp_deptno_fk references Dept,

Constraint emp_pk primary key (empno, ename)

)

 

Add a foreign key constraint (Multi-field/table-level)

SQL> ALTER TABLE employees

Add constraint emp_jobs_fk foreign key (job, deptno)

References jobs (jobid, deptno)

On Delete cascade // specifies how the sub-Table handles the deletion of records in the parent table!
 

[Important] modify the reference behavior defined by the foreign key constraint (delete cascade/delete set null/delete no action). The default value is delete on action.

Reference behavior (when a record in the primary table is deleted, determine how to process the external code segment in the subtable ):

Delete cascade: delete all related records in the subtable

Delete set NULL: set the value of the external code segment of all related records to null.

Delete no action: no action

 

10. Delete the original foreign key constraint and then add the constraint.
SQL> select constraint_name, table_name from user_constraints;

Constraint_name table_name

------------------------------------------------------------

Sys_c0017204 bkeep1

Emp_pk bkeep2

SQL> ALTER TABLE bkeep2 drop constraint emp_pk

Table altered.

 

SQL> ALTER TABLE employees drop constraint emp_deptno_fk;

SQL> ALTER TABLE employees add constraint emp_deptno_fk foreign key (deptno) References dept (deptno) on Delete cascade;

 

11. Check Constraints
· The expression of the check constraint must reference one or more fields in the table, and the calculation result of the expression must be a Boolean value.

· Can be defined at the table level or field level

· Multiple check constraints can be defined for the same field, and not null constraints can also be defined.

　

SQL> Create Table bkeep9

2 (SAL number (7,2)

3 constraint bkeep9_sal_ck1 check (SAL> 0 ));

Table created.

 

SQL> RUN

1 alter table bkeep9

2 * Add constraint bkeep9_sal_ck2 check (SAL> 2000) // multiple check entries can be added to the same field.

Table altered.

 

12. Delete Constraints
SQL> ALTER TABLE dept drop unique (dname, Loc) // specify the definition content of the constraint
SQL> ALTER TABLE dept drop constraint dept_dname_loc_uk // specify the constraint name
 

[Important] When deleting a constraint, the index corresponding to the constraint will be deleted by default. If you want to retain the index, use the keep index keyword.

SQL> ALTER TABLE employees drop primary key keep Index

 

[Important] If the constraint to be deleted is being referenced by other constraints, you can delete the constraint that references the constraint by specifying the cascade keyword in the alter table .. drop statement.

 

[Important] When the following statement is used to delete the primary key constraint in the dept table, the foreign key constraint that references this constraint in other tables will also be deleted:

SQL> ALTER TABLE dept drop primary key Cascade

 

13. Disable/activate constraints (disabling/activating constraints will cause deletion and re-indexing operations)
Method 1:
SQL> ALTER TABLE bkeep3 disable constraint bkeep3_pk;

Table altered.

SQL> ALTER TABLE bkeep3 enable constraint bkeep3_pk;

Table altered.

SQL> ALTER TABLE employees disable/enable unique email

 

Method 2:
SQL> ALTER TABLE bkeep3 modify constraint bkeep3_pk disable;

Table altered.

SQL> ALTER TABLE bkeep3 modify constraint bkeep3_pk enable;

Table altered.

 

[Important]

If the foreign key constraint is referencing the unique or primary key constraint, the unique or primary key constraints cannot be disabled,

In this case, you can disable the foreign key constraint first, and then disable the unique or primary key constraint. Alternatively, you can disable the unique or primary key constraint in the alter table... specify the cascade keyword in the disable statement. In this way, the foreign key constraints that reference them are disabled while the unique or primary key constraints are disabled, for example:

SQL> ALTER TABLE employees disable primary key Cascade

 

14. constraint Data Dictionary
· All _ constraints/dba_constraints/user_constraints

The basic information of the constraint, including the constraint name, type, and status.

(Constraint type: C (check constraint), P (primary code constraint), R (external code constraint), u (unique code constraint ))

 

· All _ cons_columns/DBA/user field information corresponding to the Constraint
SQL> DESC user_cons_columns;

Name null? Type

-----------------------------------------------------------------------------

Owner not null varchar2 (30)

Constraint_name not null varchar2 (30)

Table_name not null varchar2 (30)

Column_name varchar2 (4000)

Position Number

SQL> Col constraint_name format A24

SQL> Col column_name format A30

SQL> select constraint_name, column_name from user_cons_columns;

Constraint_name column_name

------------------------------------------------------

Sys_c0017219 last_name

Sys_c0017221 hire_date

Sys_c0017222 job_id

Emp_ename_phone_uk ename

Emp_ename_phone_uk phone