Oracle Database (10)--constraints

(a) What is a constraint

Constraint is a mandatory requirement at the table level

There are five types of constraints:

• NOT null: Non-empty
• Unique: the only
• PRIMARY Key: Primary key
• FOREIGN Key: Foreign key
• Check: Checking

Attention constraint

• If you do not specify a constraint name, Oracle server automatically specifies the constraint name in the SYS_CN format
• Create and modify constraints:

1. While building the table
2. After the table is built

• Constraints can be defined at the table-level or column-level
• Constraints can be viewed from the data dictionary view

(ii) Table-level constraints and column-level constraints

• Scope of Action:
• ① Column-level constraints only work on one column
• ② table-level constraints can function on multiple columns (of course, table-level constraints can also work on a column)
• Defined by: The column constraint must follow the definition of the column, and the table constraint is not associated with the column, but is defined separately.
• A non-null (NOT NULL) constraint can only be defined on a column

Constraint definition Syntax:

CREATE TABLE [schema.] Table (

column datatype [DEFAULT expr] [column_constraint],

...

[Table_constraint]

[,...]);

(iii) Create constraint definition 1, NOT NULL constraint

After defining a non-null constraint, assigning NULL will cause an error

2. Unique Constraint

Can be defined at the table-level or column-level

The column that defines the constraint does not allow duplicate values

3. PRIMARY KEY constraint

Can be defined at the table-level or column-level

Primary key feature: non-null and unique is primary key = NOT NULL + unique

4. FOREIGN KEY constraint

Can be defined at the table-level or column-level

When you add data, the value of the column for the FOREIGN KEY constraint must have a value in the parent table

FOREIGN KEY constraint keyword

• FOREIGN KEY: Specify columns in a child table at the table level
• REFERENCES: Columns that are marked in the parent table
• On Delete CASCADE (cascade delete): When a column in the parent table is deleted, the corresponding column in the child table is also deleted
• On DELETE SET null (cascade NULL): The corresponding column in the child table is empty

5. Check Constraint

Define the conditions that each row must meet

(iv) Adding constraints

Use the ALTER TABLE statement:

• Add or remove constraints, but cannot modify constraints
• Effective or invalid constraint
• Add not NULL constraint to use MODIFY statement

1. Add Delete constraint

--Add a non-empty constraint on the payroll

--Remove the non-null constraint for name

--Add a unique constraint to name

2. Invalid constraint

3. Activation constraints

• An enable clause activates the currently invalid constraint
• The system automatically creates a unique or PRIMARY key index when a unique or PRIMARY key constraint is defined or activated

4. Query constraints (Learn)

--Query the constraint information in the Employees table

5. Query the column that defines the constraint

--Querying columns that define constraints in the Employees table

(v) Summary

1. Create and modify constraints

2. Describe the type of constraint:

• Not NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

Oracle Database (10)--constraints