OracleDB constraints overview _ MySQL

OracleDB constraints overview bitsCN.com

Oracle DB constraints overview

• Constraints are used to force various rules at the table level.

• Constraints are used to prevent table deletion when relevance exists.

• The following constraints are valid:

-Not null: specifies that this column cannot contain NULL values.

-UNIQUE: set the value of a column or column combination to be UNIQUE for all rows in the table.

-Primary key: uniquely identifies each row in the table.

-Foreign key: after a connection is established between the column and the column of the referenced table, the reference integrity is enforced. in this way, the value of one table matches the value of another table.

-CHECK: the specified conditions must be true.

Oracle Server uses constraints to prevent invalid data from being input into tables.

You can use constraints to complete the following tasks:

• When a row is inserted, updated, or deleted in a table, various rules are enforced for the data in the table. The operation succeeds only when the constraints are met.

• Prevent deleting a table when it is correlated with other tables.

• Provides rules for Oracle tools (such as Oracle Developer.

Constraints

• You can name the constraints or generate a name in SYS_Cn format by Oracle Server.

• You can create constraints in any of the following ways:

-Create constraints when creating a table

-After creating a table

• Constraints can be defined at the column or table level.

• You can view constraints in the data dictionary.

All constraints are stored in the data dictionary. If a meaningful name is specified for the constraints, it is easier to reference them. The constraint condition name must follow the standard object naming rules, but it cannot be the same as another object name of the same user. If you do not name the constraints, Oracle Server will generate a name in SYS_Cn format, where n is an integer, so that the constraint name is unique.

You can define constraints when creating a table or after creating a table.

You can define constraints at the column or table level. In terms of function, table-level constraints and column-level constraints have the same effect.

Define constraints

• Syntax:

Create table [schema.] table

(Column datatype [DEFAULT expr]

[Column_constraint],

...

[Table_constraint] [,...]);

• Column-level constraint condition syntax:

Column [CONSTRAINT constraint_name] constraint_type,

• Table-level constraint condition syntax:

Column ,...

[CONSTRAINT constraint_name] constraint_type

(Column ,...),

The example shows the syntax for defining constraints when creating a table. You can create constraints at the column or table level.

When defining a column, the constraints defined at the Column level are included. When the table definition ends, the table-level constraints must be defined. a group of column or column combinations with constraints must be referenced in a group of brackets. The two are mainly syntactically different. In addition, the column-level constraints and table-level constraints have the same function.

The not null constraint must be defined at the Column level.

You must define constraints that apply to multiple columns at the table level.

In this syntax:

Schema: same as the owner's name

Table: The table name.

DEFAULT expr: specifies the DEFAULT value used when values are omitted in the INSERT statement.

Column: column name

Datatype: indicates the data type and length of a column.

Column_constraint: integrity constraints that are part of the column definition.

Table_constraint: integrity constraints that are part of a table definition.

• Column-level constraints:

Create table employees (

Employee_id NUMBER (6) CONSTRAINT emp_emp_id_pk primary key,

First_name VARCHAR2 (20 ),

...);

• Table-level constraints:

Create table employees (

Employee_id NUMBER (6 ),

First_name VARCHAR2 (20 ),

...

Job_id VARCHAR2 (10) not null,

CONSTRAINT emp_emp_id_pk primary key (EMPLOYEE_ID ));

Generally, constraints are created when a table is created. You can add constraints to a table after creating a table, or temporarily disable constraints.

In this example, the primary key constraint is created for the EMPLOYEE_ID column of the EMPLOYEES table.

1. the first example uses the column-level syntax to define constraints.

2. the second example uses table-level syntax to define constraints.

Not null constraints

The not null constraint ensures that a column does NOT contain NULL values. By default, columns without the not null constraint can contain NULL values.

The not null constraint must be defined at the Column level.

In the EMPLOYEES table, the EMPLOYEE_ID column inherits the not null constraint because the column has been defined as a primary key. Otherwise, the not null constraint is enforced on the LAST_NAME, EMAIL, HIRE_DATE, and JOB_ID columns.

UNIQUE constraints

UNIQUE keyword integrity constraints require that each value in a column or a group of columns (keys) must be UNIQUE, that is, in a specified column or a group of columns, no duplicate value exists for any two rows in the table. The columns (or a group of columns) included in the definition of the UNIQUE keyword constraint condition are called UNIQUE keywords. If a UNIQUE constraint is composed of multiple columns, this group of columns is called a combination of UNIQUE keywords.

UNIQUE constraints allow NULL values, unless you have defined not null constraints for the same column. In fact, because NULL values are considered NOT to be equal to any value, any number of rows can include NULL values in columns without the not null constraint. Null values in a column (or all columns that combine the UNIQUE keyword) always meet the UNIQUE constraints.

Note: Due to the search mechanism of the UNIQUE constraints on multiple columns, the non-empty columns with the UNIQUE keyword constraints of some null values cannot have the same value.

It can be defined at the table or column level:

Create table employees (

Employee_id NUMBER (6 ),

Last_name VARCHAR2 (25) not null,

Email VARCHAR2 (25 ),

Salary NUMBER (8, 2 ),

Commission_pct NUMBER (2, 2 ),

Hire_date date not null,

...

CONSTRAINT emp_email_uk UNIQUE (email ));

UNIQUE constraints can be defined at the column or table level. To create a unique combination keyword, you can define the constraint at the table level. If you cannot use a single attribute to uniquely identify a row, you need to define a composite keyword. In this case, you can create a unique keyword consisting of two or more columns. The combination value is always unique and can be used to identify rows.

In this example, the UNIQUE constraint is applied to the EMAIL column of the EMPLOYEES table. The name of the constraint is EMP_EMAIL_UK.

Note: Oracle Server creates a UNIQUE index for one or more UNIQUE keyword columns to enforce the UNIQUE constraint.

Primary key constraints

The primary key constraint is used to create the primary key for creating a table. Only one primary key can be created for each table.

The primary key constraint is a column or a group of columns that uniquely identify each row in the table. This constraint can force a column or column combination to be unique and ensure that columns that are part of the primary key do not contain null values.

Note: Because uniqueness is part of the definition of primary key constraints, Oracle Server enforces uniqueness by implicitly creating a unique index for one or more primary key columns.

Foreign key constraints

Foreign key (or reference integrity) constraints specify a column or column combination as a foreign key, and establish a relationship with the same table or different table primary keys or unique keywords.

In this example, DEPARTMENT_ID has been defined as a foreign key in the EMPLOYEES table (related table or sub-table), which references the DEPARTMENT_ID column of the DEPARTMENTS table (referenced table or parent table.

Criteria

• The foreign key value must match the existing value in the parent table or be NULL.

• The foreign key depends on the data value. The Foreign key is a pure logical pointer, not a physical pointer.

It can be defined at the table or column level:

Create table employees (

Employee_id NUMBER (6 ),

Last_name VARCHAR2 (25) not null,

Email VARCHAR2 (25 ),

Salary NUMBER (8, 2 ),

Commission_pct NUMBER (2, 2 ),

Hire_date date not null,

...

Department_id NUMBER (4 ),

CONSTRAINT emp_dept_fk foreign key (department_id)

REFERENCES parameters (department_id ),

CONSTRAINT emp_email_uk UNIQUE (email ));

You can define the foreign key constraint at the column or table level. You must use table-level definitions to create a combination of foreign keys.

In this example, a table-level syntax is used to define a foreign key constraint for the DEPARTMENT_ID column of the EMPLOYEES table. The name of the constraint is EMP_DEPT_FK.

If the constraint is only for a single column, you can also define a foreign key at the Column level. The syntax difference is that there is no keyword foreign key. For example:

Create table employees

(...

Department_id NUMBER (4) CONSTRAINT emp_deptid_fk

REFERENCES parameters (department_id ),

...

)

Foreign key constraint condition: keyword

The foreign key is defined in the sub-table, and the table containing referenced columns is the parent table. Foreign keys are defined using a combination of the following keywords:

• Foreign key is used to define columns in a subtable at the table constraints level.

• REFERENCES is used to identify tables and columns in the parent table.

• On delete cascade indicates that the row in the parent table is also deleted from the child table.

• On delete set null indicates that the foreign key value is SET to NULL when the row in the parent table is deleted.

The default behavior is called a restriction rule. this rule can be used to prohibit update or deletion of referenced data.

When the on delete cascade or on delete set null option is not available, if a row in the parent table is referenced in the child table, the row cannot be deleted.

CHECK constraints

• Define a condition that each row must meet

• The following expressions are not allowed:

-Expression that references the CURRVAL, NEXTVAL, LEVEL, and ROWNUM false columns

-Expression for calling the SYSDATE, UID, USER, and USERENV functions

-Query by referencing other values in other rows

..., Salary NUMBER (2)

CONSTRAINT emp_salary_min CHECK (salary> 0 ),...

CHECK constraints are used to define a condition that each row must meet. This condition can use the same structure as the query condition, except for the following:

• Expressions that reference the CURRVAL, NEXTVAL, LEVEL, and ROWNUM false columns

• Expressions that call the SYSDATE, UID, USER, and USERENV functions

• Query that references other values in other rows

A column can have multiple CHECK constraints that will be referenced in its definition. You can define any number of CHECK constraints for a column as needed.

You can define CHECK constraints at the column or table level.

Create table employees

(...

Salary NUMBER (8, 2) CONSTRAINT emp_salary_min CHECK (salary> 0 ),

...

The example shows the statement used to create the EMPLOYEES table in the HR solution.

Create table employees

(Employee_id NUMBER (6)

CONSTRAINT emp_employee_id PRIMARY KEY

, First_name VARCHAR2 (20)

, Last_name VARCHAR2 (25)

CONSTRAINT emp_last_name_nn NOT NULL

, Email VARCHAR2 (25)

CONSTRAINT emp_email_nn NOT NULL

CONSTRAINT emp_email_uk UNIQUE

, Phone_number VARCHAR2 (20)

, Hire_date DATE

CONSTRAINT emp_hire_date_nn NOT NULL

, Job_id VARCHAR2 (10)

CONSTRAINT emp_job_nn NOT NULL

, Salary NUMBER (8, 2)

CONSTRAINT emp_salary_ck CHECK (salary> 0)

, Commission_pct NUMBER (2, 2)

, Manager_id NUMBER (6)

CONSTRAINT emp_manager_fk REFERENCES

Employees (employee_id)

, Department_id NUMBER (4)

CONSTRAINT emp_dept_fk REFERENCES

Parameters (department_id ));

You can use the following statements to query which columns have the following constraints.

Hr @ TEST0924> l

1 select a. owner, a. TABLE_NAME, B. COLUMN_NAME, a. CONSTRAINT_NAME, a. CONSTRAINT_TYPE, a. SEARCH_CONDITION

2 from USER_CONSTRAINTS a, USER_CONS_COLUMNS B

3 * where a. CONSTRAINT_NAME = B. CONSTRAINT_NAME and A. table_name = 'ployees'

Hr @ TEST0924>/

OWNER TABLE_NAME COLUMN_NAM CONSTRAINT_NAME C SEARCH_CONDITION

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

Hr employees salary EMP_SALARY_MIN C salary> 0

Hr employees email EMP_EMAIL_UK U

Hr employees EMPLOYEE_ID EMP_EMP_ID_PK P

Hr employees DEPARTMENT_ID EMP_DEPT_FK R

Hr employees JOB_ID EMP_JOB_FK R

Hr employees MANAGER_ID EMP_MANAGER_FK R

Hr employees LAST_NAME EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL

Hr employees email EMP_EMAIL_NN C "EMAIL" IS NOT NULL

Hr employees HIRE_DATE EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL

Hr employees JOB_ID EMP_JOB_NN C "JOB_ID" IS NOT NULL

10 rows selected.

Violation of constraints

If you attempt to violate the constraint rules after setting constraints on a column, an error is returned. For example, if you try to update a record, but the value in the record is subject to integrity constraints, an error is returned. In the example, because the parent table partition does not have Department 55, you will receive the violation ORA-02291 "parent key not found (parent keyword not found )".

Hr @ TEST0924> update employees set department_id = 55 where department_id = 110;

Update employees set department_id = 55 where department_id = 110

*

ERROR at line 1:

ORA-02291: integrity constraint (HR. EMP_DEPT_FK) violated-parent key not found

If a row contains a primary key used as a foreign key in other tables, the row cannot be deleted.

Hr @ TEST0924> delete from orders ments where department_id = 60;

Delete from orders ments where department_id = 60

*

ERROR at line 1:

ORA-02292: integrity constraint (HR. EMP_DEPT_FK) violated-child record found

If you try to delete a record, but the value in the record is subject to integrity constraints, an error is returned.

In the example, you try to delete Department 60 from the DEPARTMENTS table, but this operation causes an error because the department number is used as a foreign key in the EMPLOYEES table. If you try to delete a parent record with a child record, you will receive the violation ORA-02292 "child record found (with a child record )".

The following statement is valid because department 210 does not have any employees:

Hr @ TEST0924> delete from orders ments where department_id = 210;

1 row deleted.

BitsCN.com