Oracle Database Objects _ constraints

Constraint is a kind of mandatory rule which is added on the table, and it is an important means to guarantee the integrality of data.

When you insert data into a table, or modify data in a table, you must meet the conditions specified by the constraint.

When designing the structure of a table, you should take full account of the constraints that need to be imposed on the table.
There are roughly three ways to ensure data integrity, namely application code, triggers, and constraints.

The constraint is a more flexible way, it not only maintenance more convenient, but also high performance, is the best choice to ensure data integrity.
constraints can be specified when a table is created, or after a table is created .

If you specify a constraint after the table is created, the constraint cannot be imposed because some data already exists in the table that does not meet the criteria.

Types of Constraints

The following five constraints can be imposed on the table:
• Notnull non-null constraint
• unique uniqueness constraints
•PRIMARY key PRIMARY KEY constraint
•FOREIFN key foreign KEY constraint
• Check checking constraints
The NOT NULL constraint specifies that the value on a column cannot be null.

When you insert a row of data into a table using an INSERT statement, or when you modify a row of data by using the UPDATE statement, you must specify a value for the column, and you cannot make it empty.
The unique constraint specifies that the data on a column must be unique and cannot have duplicate values, but allow null values.

The PRIMARY key constraint is a PRIMARY KEY constraint.

A primary key is used to uniquely identify a row of data in a table, which specifies that the data on the primary key column cannot be duplicated and cannot be empty.
Each reasonably designed table should have a primary key. The primary key can be a column or a combination of multiple columns.
If you specify a PRIMARY KEY constraint on a column, you do not need to specify a Notnull constraint and a unique constraint on that column.

You can create only one primary key on a table.

When a primary key is created, a unique index is automatically created on the primary key column with the same name as the constraint.
FOREIFN key is a foreign key constraint.

A foreign key is used to establish an association with another table.

The correlation between the two tables is maintained by the primary key and foreign key.
A foreign key specifies that the data in the column must be another data in the primary key column of the table associated with it.
A foreign key can be a column, or it can be a combination of multiple columns.

There can be only one primary key in a table, but there may be multiple foreign keys.

A check constraint is a relational expression that specifies a condition that a column must meet.

Note: Both primary and foreign KEY constraints implicitly create a unique index with the same index name as the constraint name.

Conversely, uniqueness constraints are also implicitly created when a uniqueness index is created.

How to specify a constraint when creating a table

If created at the same time as the table, specify the name and constraint type of the constraint through the constraint keyword in the Create statement that creates the table.
The CREATE statement format for creating tables and constraints at the same time is:

CREATE Table Table name (
Column 1 data type constraint constraint name 1 constraint type,
Column 2 data type constraint constraint name 2 constraint type,
...

);

Where the constraint name is the unique name specified for the constraint.

The constraint name can be specified by the user, or it can be generated automatically.

If you omit the keyword constraint and the constraint name, the constraint name is automatically generated.

If the constraint name is automatically generated, the table and constraint type that the constraint resides on cannot be judged by such a name.
If the user specifies the constraint name themselves, you can include useful information such as the table name, constraint type, and so on in the name.
If you specify a constraint on the column after the definition of a column, this method of defining the constraint is called a column-level constraint .

Constraints can be specified after each column definition, or after the definition of all columns.

If a constraint is specified at the end of a table definition, such a constraint definition method is called a table-level constraint .
If a constraint involves a combination of multiple columns, you cannot specify a constraint after each column, but only a table-level constraint.
The CREATE statement format that defines table-level constraints is:

CREATE Table Table name (
Column 1 data type,
Column 2 data type,
··
CONSTRAINT constraint name L constraint type (column name),
CONSTRAINT constraint Name 2 constraint type (column name),
. . . );

In five constraints, the NOT NULL constraint can be defined only as a column-level constraint, and the remaining four can be defined either as column-level constraints or as table-level constraints because table-level constraints are defined after all columns, not after a specific column, so you specify related column names in a table-level constraint.

The definition of a FOREIGN key constraint is complex because the foreign key is associated with the primary key of the other table, so you need to specify not only the type of the constraint and the columns concerned, but also which column of the table to associate with.
If you define a FOREIGN key constraint at the column level, the format defined is:

CONSTRAINT constraint name REFERENCES table name (column name);

Where the constraint name is the name of the foreign KEY constraint.

FOREIGN key is a constraint type, which is a foreign key constraint.

The REFERENCES keyword specifies which column of the table to associate with.

If you want to define a foreign key constraint at the table level, the definition code for the foreign key is placed after the definition of all columns, which is in the following format:
CONSTRAINT Constraint name FOREIGN key (foreign key column) REFERENCES table name (column name);

A constraint is a database object attached to a table, and its information is also recorded in a data dictionary.

There are two data dictionaries related to constraints, one is user_constraintsand the other is user_cons_columns .
In the data dictionary user_constraints, the information about the constraints of the current user is recorded, such as constraint name, constraint type, table of constraint, state of constraint, etc.
If it is a foreign key, the name of the primary key associated with it is also recorded.

For example:

If you want to further query the constraint on which column to apply, you need to query another data dictionary, it is user_ cons_ COLUMNS .

How to specify a constraint after creating a table

Oracle Database Objects _ constraints