Example Analysis of data Integrity in oracle

Editor to share with you a sample analysis of data integrity in oracle. I hope you will gain something after reading this article. Let's discuss it together.

1. Introduction to the integrity of data

That is, correctness and accuracy, including three kinds: physical integrity, referential integrity,

User-defined integrity. In Oracle, mainly through constraints, triggers, procedure functions.

It came true. The following is about achieving data integrity through constraints.

Give examples of two tables:

Student (student number, name, department number)

Department (Department number, Department name)

Employee (number, name, date of birth, date of work)

So:

The student number is the primary key column, and its value cannot be wrong. If it cannot be negative, this is the entity integrity.

The department number is a foreign key, and the department number of the student table refers to the system table, that is to say, the value of the department number of the student table must be within the value range of the department number of the department table. This is referential integrity.

The birth date of the employee table must require Xiao Yu's work date, which is the user-defined integrity.

two。 Maintain the integrity of data, there are not null, unique, check, primary key, foreign key five.

Not null such as the name of the student; unique such as the student's student number; primary key primary key, foreign key foreign key, check can be checked automatically according to user requirements.

Unique cannot be repeated, but can be empty.

Primary key cannot be repeated or empty.

A table can have only one primary key, but can have multiple unique.

Not null can only be defined within a column, and the other four constraints can be defined within the table after the column definition is finished.

Not null example (where the not null constraint is automatically named by the system):

Create table yg2 (bh number (4) not null,xm varchar2 (8))

Check example:

Create table yg3 (bh number (4) not null check (bh > 0 and bh4)

3.3 Delete the constraint:

Alter table table name drop constraint constraint name

Alter table department drop constraint deptName_check;-remove the deptName_check constraint on the department table you just created

Delete primary key statement: alter table table name drop primary key

If there is an error: ORA-02273: this unique / primary key has been referenced by some foreign keys, you can: alter table department drop primary key cascade

If you delete a table whose primary key is referenced, you can: drop table table name cascade constraints

After reading this article, I believe you have some understanding of "sample Analysis of data Integrity in oracle". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!