Related terminology in the database

In the Oracle database, each database contains many objects, including tables, views, stored procedures, triggers, and their constraints.

　　1. Table

tables, which are data tables for storing data in a database. Each database can contain many tables, but the names of each data table cannot be duplicated. Each row of the table represents a record, each column has a column name, the column name is unique, and the intersection of the row and column is called a field.

　　2. View

A view is a virtual table of a database. In the view, the records are queried from the database tables, and the views are used primarily to facilitate information queries and to shorten the time to query data.

　　3. Stored Procedures

A stored procedure is a block of code that consists of SQL statements and control flow statements. The stored procedure is within the database and can be executed by the application through the name of the stored procedure.

Stored procedures in the development of software, you can put a large number of data operations on the server side of the stored procedures, and only return the required data, thus reducing the amount of data transmission, speed can be greatly improved.

　4. Trigger

Triggers are special stored procedures and are composed of SQL statements and program control statements. However, triggers do not need to be called in the database, but automatically. For example, in a trigger you can define what is executed in a trigger after a table record has been modified.

　　5. Constraints

Constraints are the means of guaranteeing the integrity of data in a database table in a database. The constraints used in Oracle have PRIMARY KEY constraints, FOREIGN KEY constraints, UNIQUE constraints, check constraints, and non-null constraints of 5, where primary KEY constraints and UNIQUE constraints are considered unique constraints, and the outer key constraint is considered a referential constraint.

　　(1) Primary key (Primary key) constraint

A PRIMARY KEY constraint can have only one in each data table, but a PRIMARY KEY constraint may consist of multiple columns, typically a primary key consisting of multiple columns called a composite primary key or a combined primary key. A PRIMARY KEY constraint guarantees that the data for the primary key column does not have a duplicate value and that the value is not empty, or that it is a unique record in the identity table.

　　(2) foreign key (Foreign key) constraint

A FOREIGN KEY constraint is considered a referential constraint because it is primarily used to correlate data in one table with data from another table, and the association between tables and tables is to ensure the integrity of the data in the database, and the use of foreign keys to ensure data integrity, also known as referential integrity.

　　(3) only (unique) constraint

A unique constraint, like a primary key constraint, is a constraint that sets a column in a table that cannot be duplicated, except that there can be only one PRIMARY KEY constraint in a table and multiple unique constraints. Typically, the purpose of setting a unique constraint is to make the non-primary key column have no duplicate values. Another difference between a unique constraint and a PRIMARY KEY constraint is that if a column in the datasheet has a null value, the column cannot be set as the primary key column, but it can be set as a unique constraint.

　　(4) Check Constraint

A check constraint is a range of values used to specify the values of columns in a table. For example, if you want to make an employee's age column A value of 18~50 in the Employee Information table, you can set it with a check constraint and an error occurs when the value entered is not within the valid range. This guarantees the validity of the data in the database.

　　(5) Non-null (NOT NULL) constraint

Non-null constraints are used to constrain columns in a table from being allowed to be empty. For example, a non-null constraint can be used to guarantee that the column cannot be empty when the Employee ID number column in the Employee Information table is required for the employee to enter.

　　Integrity of database Design

Using database constraints is the way to ensure database integrity. The integrity of database design is actually to ensure the correctness of the data. To ensure the correctness of the data, there are 3 major integrity involved in Oracle, namely entity integrity, zone integrity, and referential integrity.

1. Entity integrity

The primary key fields in the Entity Integrity requirements table cannot be empty or duplicate values. For example, in school each student's number is unique, the card number of the bank card is also unique, each person's identity card number is also unique.

2. Regional integrity

Zone integrity is guaranteed to be entered into the database

Related terminology in the database