Oracle BASICS (8) data integrity and oracle integrity

Oracle BASICS (8) data integrity and oracle integrity

I. Data Integrity

Data integrity requires that the data in the database be accurate. Accuracy is achieved through the design and constraints of database tables. To achieve data integrity, the database needs to do the following:

• Make sure that the data in each row meets the requirements.
• If no data is reported, the data meets the requirements.

To meet the preceding requirements, Oracle provides four types of constraints (Constraint ).

　　

　　1. entity integrity constraints

Entity integrity requires that each row of data in the table reflect different entities and cannot have the same data row. Entity integrity is achieved through primary key constraints and unique constraints.

(1) primary key constraints:

The values of one or more columns in a table can be used to uniquely represent each row in the table. Such a combination of one or more columns is called the primary key of the table.

The primary key is unique and cannot be empty. A table can only have one primary key. The primary key ensures that the data rows in the table are unique. In the same table, multiple columns can be used as the primary key. When selecting a primary key, two principles should be followed, minimum and stability.

(2) unique constraints:

Ensure that the data in a column in the Data row is unique and not repeated. In this way, you can add unique constraints.

The difference between a unique constraint and a primary key:

• The primary key cannot be blank. The unique key can be empty, but only one null value is allowed.
• A primary key has only one table and can have multiple unique tables.
• A primary key can be composed of multiple tables and can only be a single column.
• A primary key can be used as a foreign key for other tables.

　　

　　2. Domain integrity:

Integrity refers to the validity of a given input. It is implemented by data type, check constraints, input format, foreign key constraints, default values, non-empty constraints, and other methods.

　　

　3. Integrity of reference:

Integrity of reference refers to the association between two tables to ensure data integrity. For example, for a class or a student, the student's class must exist in the class table, otherwise there will be inaccuracy. Integrity of reference can be achieved through foreign key constraints.

 

　4. Custom integrity:

You can customize constraints. It is mainly implemented through stored procedures and trigger objects.

 

Ii. Add Constraints

When creating a table, we can add various constraints after the field. The column-Level Definition and table-level definition are as follows:

　　1. Column-Level Definition

Column-level definition refers to defining constraints while defining columns.

For example, the primary key and unique constraint are defined in the t_class table.

-- Create table t_class (cid number constraint pk_cid primary key, -- add a primary key constraint for cid cname VARCHAR2 (20) CONSTRAINT uq_sortname UNIQUE -- add a UNIQUE constraint for cname)

Note: When defining columns, you can add a constraint pk_department without a constraint. If the difference is not added, the system automatically assigns a primary key constraint name, which is less readable.

　　2. Table-Level Definition

Table-level definition refers to defining constraints after defining all columns. Note: not null can only be defined at the column level.

Take the primary key constraint and foreign key constraint defined when creating a student and class table as an example:

-- Create table t_student (stuID number (4), stuname varchar2 (20) not null, stupass varchar2 (20) not null, cid number not null, constraint uq_username UNIQUE (stuname), -- create a UNIQUE constraint ck_userpass Check (LENGTH (stupass)> 3), -- create a Check constraint (password LENGTH greater than 3 characters) constraint pk_uid primary key (stuID), -- primary key constraint fk_cid foreign key (cid) references t_class (cid) -- foreign key constraint );

　　3. After creating a TABLE, you can add constraints to the TABLE that has been created. You must use the alter table statement.

Syntax: alter table add constraint constraints description of the CONSTRAINT type.

Alter table t_student add constraint uq_username UNIQUE (stuname) -- create a unique constraint add constraint ck_userpass Check (LENGTH (stupass)> 3) -- create a Check CONSTRAINT (the password LENGTH is greater than 3 characters) add constraint pk_uid primary key (stuID) -- primary key constraint add constraint fk_cid foreign key (cid) references t_class (cid) -- foreign key CONSTRAINT

 

Iii. Delete Constraints

Syntax: alter table dropconstraint constraint name

ALTER TABLE t_student    DROP CONSTRAINT PK_UID

 

Iv. View Constraints

View All constraints in the T_STUDENT table. Note: The description must be in uppercase; otherwise, no results can be found.

SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'T_STUDENT'