Nine constraints on oracle9i Study Notes

1. constraint type
Not null ---- the specified Column cannot contain null values
Unique ----- the value of a specified column or the value of a combination of columns must be unique for all rows in the table.
Primary Key ---- unique identifier of each row in the table
Foreign key ---- create and force a foreign key relationship between a column in the referenced table
Check ---- specify a condition that must be true

2. Constraints
1) naming a constraint must follow the naming rules. Without naming constraints, the Oracle server generates a name in sys_cn format. Here N is a unique integer, so the constraint name is unique.
2) You can create a constraint when creating a table or after creating a table.
3) define a constraint at the column or table level.
4) view constraints in the data dictionary user_constraints

3. Define Constraints
Create Table [schema.] Table
(Column datatype [Default expr]
[Column_constraint],
...
[Table_constraint]);
Create Table EMP (
Empno number (4 ),
Ename varchar2 (10 ),
...
Deptno number (7,2) not null,
Constraint emp_empno_pk primary key (empno ));

1) column constraint level: Only one column can be referenced, which is a column definition.
Column [constraint constraint_name] constraint_type,

2) Table constraint level: one or more columns can be referenced, which is a table definition and can be defined as a constraint other than not null.
Column ,...
[Constraint constraint_name] constraint_type
(Column ,...),

3) constraints are usually created when a table is created. After a table is created, the constraint can be added and temporarily disabled.
Constraints can be defined at two levels:

Constraints
A column only involves a single column, which is defined in a standard way;
Any type that can define integrity constraints
The table involves one or more columns, and the columns in the table are defined separately;
Can define any constraints except not null

4. Not null Constraint
-Make sure that some columns do not allow null values:
-Not null constraint: For this column, no row can contain a null value.
-The not null constraint is missing: For this column, any row can contain null values.
-Not null constraints ensure that the column does not contain control. By default, this constraint does not exist and can contain null values.

Example 1: define constraints at the column level
Create Table EMP (
Empno number (4 ),
Ename varchar2 (10) Not null,
Job varchar2 (9 ),
Mgr number (4 ),
Hiredate date,
Sal number (7,2 ),
Comm number (7, 2 ),
Deptno number (7,2) not null );

5. unique constraints
-The unique Integrity Constraint requires that the specified column or column combination in the table cannot have two rows with the same value. A column (or column combination) that defines a unique constraint is called a unique key)
-Unless the not null constraint is defined in the corresponding column, the unique constraint allows null input.
-Unique can be defined at the column level or at the table level.

Example 1: defined at the table level
Create Table deptment (
Deptno number (2 ),
Dname varchar2 (14 ),
Loc varchar2 (13 ),
Constraint dept_dname_uk unique (dname ));

Example 2: defined at the column level
Create Table deptment (
Deptno number (2 ),
Dname varchar2 (14) Constraint dept_dname_uk unique,
Loc varchar2 (13 ));

6. Primary Key constraints
-The primary key constraint creates a primary key for the table.
-Each table can only create one primary key.
-The primary key constraint is a column or column combination that uniquely identifies a row in a table. This constraint forces the uniqueness of a column or column combination.
-The primary key column cannot contain null values.
-It can be defined at the table or column level.

Example 1: defined at the table level
Create Table deptment (
Deptno number (2 ),
Dname varchar2 (14 ),
Loc varchar2 (13 ),
Constraint dept_dname_uk unique (dname ),
Constraint dept_deptno_pk primary key (deptno ));

Example 2: defined at the column level
Create Table deptment (
Deptno number (2) Constraint dept_deptno_pk primary key,
Dname varchar2 (14 ),
Loc varchar2 (13 ),
Constraint dept_dname_uk unique (dname ));

7. Foreign Constraints
-It can be defined at the table or column level.
For example, column-level constraints:
Create Table EMP (
Empno number (4 ),
Ename varchar2 (10) Not null,
Job varchar2 (9 ),
Mgr number (4 ),
Hiredate date,
Sal nubmer (7,2 ),
Comm number (7, 2 ),
Deptno number (7,2) not null
Constraint emp_deptno_fk references dept (deptno ));
-Foreign key: Specifies the table or column constraint level.
-References: identifies columns in the parent table and parent table.
-On Delete cascade: When the row in the parent table is deleted, the dependent row in the child table is deleted.
-On Delete set NULL: when the value of the parent table is deleted, the conversion foreign key value is empty.
-When the on Delete cascade or on Delete set null option is not available, if the row in the parent table is referenced in the subtable, it cannot be deleted.

Example: On Delete cascade Option
Create Table Test (
Empno number (4 ),
Ename varchar2 (10) Not null,
Deptno number (7,2) not null
Constraint emp_deptno_fk
References dept (deptno)
On Delete cascade );

8. Check Constraints
-The check constraint defines the conditions that each row must meet. The condition can be structured in the same way as the query condition.
Salary number (8, 2) Constraint
Department_salary_min check (salary> 0 ),
...
-The following expressions are not allowed:
-Reference the currval, nextval, level, and rownum pseudo columns.
-Call the sysdate, uid, user, and uderenv functions.
-Values involving other rows

9. Change Constraints
Use the alter table statement:
-Add or delete Yushu without modifying its structure
-Enable or disable Constraints
-Use the modify clause to add a not null Constraint

1) Add Constraints
Alter table table
Add [constraint] type (column );
Where:
Table ---- table name
Constraint ---- name of the constraint
Type ---- type of the constraint
Column ---- name of the column affected by the Constraint

Example 1: Add a constraint to the EMP table, requiring the salary to be greater than zero
ALTER TABLE EMP
Add constraint emp_sal
Check (SAL> 0 );

Example 2: Add a foreign key constraint to the EMP table to indicate that the manager must already be an employee in the EMP table.
ALTER TABLE EMP
Add constraint emp_mgr_fk
Foreign key (MGR)
References EMP (empno );

You can use the modify clause of the alter table statement to add a not null constraint to an existing column.
Example:
ALTER TABLE EMP
Modify (salary constraint emp_salary_nn not null );
Note: not null can be defined only when it indicates null or each row has a non-null value.

2) view Constraints
-Data Dictionary user_constraints
Select table_name, constraint_name, constarint_type, status
From user_constraints
Where table_name = 'emp'; // The table name must be in uppercase.

-In constraint_type, c Indicates check, P indicates primary key, r indicates integrity of reference, and u indicates unique key. Not null is stored as a check constraint in the data dictionary.

-Use the user_cons_columns data dictionary view to view the column names related to constraints. This view is particularly useful for the constraints specified by the system name.

Select constant_name, column_name
From user_cons_columns
Where table_name = 'emp ';

3) Disable Constraints
Syntax:
Alter table teble
Disable constraint [cascade];
Where:
Table ---- table name
Constraint ---- constraint name
Note: You can use the disable clause in the create table statement or the alter table statement.
The cascade clause disables the Integrity Constraint of the dependency.

Example: alter table test_p
Disable constraint test_deptno_pk cascade;
-Execute the disable clause of the alter table statement to disable integrity constraints. You can disable a constraint without deleting it.
-Disable the Integrity Constraint of the desired dependency using the cascade option.
-Disabling the unique key or primary key will remove the unique index.

4) enable constraints
Syntax:
Alter table table
Enable constraint;

Example: alter table test_p
Enable constraint test_deptno_pk;
-If a constraint is enabled, the constraint applies to all data in the table. All data in the table must be applicable to the constraint.
-If a uniqe key or primary key constraint is enabled, a unique or primary key index will be automatically created.
-Enable clauses can be used in create table or alter table statements.
-Enable a disabled primary key constraint with the cascade option. No foreign keys dependent on the primary key are enabled.

5) delete Constraints
Syntax:
Alter table table
Drop primary key | unique (column) |
Constraint constraint [cascade];
Where:
Table ---- table name
Column ---- name of the column affected by the Constraint
Constraint ---- name of the constraint
The cascade option also deletes any constraints on which it depends.

Example 1: Delete the manager constraint from the EMP table
ALTER TABLE EMP
Drop constraint emp_mgr_fk;

Example 2: delete the primary key constraint on the test_p table and delete the foreign key constraint associated with the test_f.deptno column.
Alter table test_p
Drop primary key cascade;

To delete a constraint, follow these steps:
-Determine the constraint name from the user_constraints and user_cons_columns data dictionary views.
-Use the alter table statement with the drop clause
-The cascade option of the drop clause causes any constraints on which the clause is dependent to be deleted.
 

Exercise
1. Create a table test_f
Column Name Data Type Length Primary Key null No
ID number 7 Yes
Name varchar2 25 not null

Create Table test_f
(ID number (7 ),
Name varchar2 (25) not null,
Constraint test_fid_pk primary key (ID ));

2. Create the test_p table
Column Name Data Type Length Primary Key null No
Deptno number 8 Yes
Dept_name varchar2 25 not null

Create Table test_p
(Deptno number (8 ),
Dept_name varchar2 (25) not null,
Constraint test_pno_pk primary key (deptno ));

3. Change the test_f table and add the deptno field, type number, and length 8.

Alter table test_f
Add (deptno number (8 ));

4. Add a foreign key to the deptno column of the test_f table. Refer to the deptno column of the test_p table and name the constraint my_dept_id_fk.

Alter table test_f
Add constraint my_dept_id_fk
Foreign key (deptno)
References test_p (deptno );

5. Change the table test_f and add the Sal field. The type is number and the length is 7. The check constraint is added. SAL> 1000 is required.

Alter table test_f
Add (SAL number (7 ),
Consstraint test_fsal_c
Check (SAL> 100 ));

6. query the user_constraints view and confirm that the constraint has been added. Pay attention to the constraint type and name.

Select table_name, constraint_name, constraint_type, status
From user_constraints
Where table_name = 'test _ F ';

7. Disable the check constraint for the test_f table

Alter table test_f
Disable constraint test_fsal_c cascade;

8. Enable the check constraint for the test_f table

Alter table test_f
Enable constraint test_fsal_c cascade;

9. Delete the check constraint of the test_f table

Alter table test_f
Drop constraint test_fsal_c cascade;