Data integrity is the logical consistency and accuracy of the data in the index database. Data integrity is generally divided into three categories: domain integrity, entity integrity and reference integrity. Integrity constraints ensure data integrity by restricting data between columns, rows and tables. Integrity constraints are defined on tables and stored in data dictionaries.

 

1. Domain Integrity

Domain integrity, also known as column integrity, refers to determining whether a data set is valid for a column and whether null values are allowed. Domain integrity is usually achieved by using validity checks. It can also be achieved by limiting data types, formats or possible ranges of values.

Oracle can achieve domain integrity by checking constraints. Check constraint means that the input of a field must satisfy the condition of check constraint. If not, the data can not be input normally.

There are several ways to manipulate check constraints by using SQL statements:

(1) Create check constraints when creating tables using SQL statements

 

1. create table tableName(  
2.     columnName dataType [not null | null] [default expression] check(checkExpression),  
3.     columnName dataType [not null | null] [default expression] constraint checkName check(checkExpression),  
4.     ...n  
5. )  

 

 

Sample code:

 

1. create table person(  
2.     id int primary key,   
3.     sex varchar(4) check(sex='male' or sex='female'),  
4.     age int default 0 constraint ageCheck check(age>=0 and age<=125)  
5. )  

 

 

(2) Create check constraints when modifying using SQL statements

 

1. alter table tableName add(constraint checkName check(checkExpression), columnName dataType check(checkExpression))  

 

 

Sample code:

 

1. alter table person add(constraint sexCheck check(sex='male' or sex='female'), age int check(age>=0 and age<=125))  

 

 

(3) Delete check constraints with SQL statements

 

1. alter table tableName drop constraint  checkName;  

 

 

Sample code:

 

1. alter table person drop constraint sexCheck;  

 

 

 

2. Entity Integrity

Entity integrity, also known as row integrity, requires each row in the table to have a unique identifier, which is the primary key. Entity integrity can be achieved by indexing, unique constraints and primary key constraints.

Creating primary keys for tables can achieve the entity integrity of tables. Primary keys can be a combination of columns or columns. A table can only have one primary key constraint, and columns in primary key constraint can not be null values. When primary keys are created for tables, Oracle automatically creates a unique index for columns corresponding to primary keys to achieve the uniqueness of data. With an index, fast access to data can be achieved.

To ensure that the values of non-primary key columns in a table are not duplicated, unique constraints can be added to the column, and unique constraints can automatically generate indexes.

The differences between primary key constraints and unique constraints are as follows:

(1) A data table can only have one primary key constraint, but it can have multiple unique constraints.

(2) The value of the primary key corresponding field cannot be null, while the value of the unique constrained corresponding column can be null.

(3) The values of primary key corresponding fields or combinations of fields can not be repeated, while the values of unique constrained columns can be repeated when they are null, that is to say, unique constrained columns can have multiple null values.

Create and delete primary key constraints and unique constraints using SQL

(1) Create corresponding constraints when creating tables

 

1. create table tableName(columnName dataType primary key, columnName dataType unique,...n);  
2. create table tableName(columnName1 dataType, columnName2 dataType, ...n, primary key(columnName1, columnName2), unique(columnName3, columnName4));  

 

 

Sample code:

 

1. create table person(id int primary key, name varchar(20) unique, age int);  
2. create table person(id int, name varchar(20), age int, primary key(id), unique(name));  

 

 

(2) Create corresponding constraints when modifying tables

 

1. alter table tableName add(constraint pkName primary key(column1, column2), constraint uniqueName unique(column3, column4));  

 

Sample code:

 

1. alter table person add(constraint personPk primary key(id), constraint nameUnique unique(name));  

 

(3) Delete primary key and unique constraints

 

1. alter table tableName drop constraint constraintName;  

 

Sample code:

 

1. alter table person drop constraint personPk;  

       

 

3. Reference Integrity

Reference integrity, also known as referential integrity, ensures consistency between data from slave tables (reference tables) and data from main tables (reference tables). Reference integrity is achieved by defining the correspondence between the foreign key of the slave table and the primary key of the primary table. Foreign key can be used to define foreign keys in slave tables and primary key can be used to define primary keys in primary tables.

If a record in the master table is referenced by a foreign key in the slave table, the record in the master table can neither be deleted nor modified.

-- If the foreign key from the table is not null constrained, the foreign key from the table can also be null.

Define and delete reference relationships between tables by using SQL statements

(1) Define the reference relationship between tables at the same time when creating tables

 

1. create table tableName(columnName dataType [foreign key] references referencesTableName(referencesColumn),...n);  

 

--referencesTableName denotes the table name of the reference table, and referencesColumn denotes the field name in the reference table, which field in the reference table refers to.

Sample code:

 

1. create table saleBill(id int primary key, saleBookBillId int references saleBookBill(id));  

 

This code indicates that the saleBook BillId field in the saleBill table is the id field referring to the saleBook Bill table.

(2) Define foreign key constraints by modifying tables

 

1. alter table tableName add(constraint constraintName foreign key(column1[, column2, ...n]) references refTableName(refColumn1[, refColumn2, ...n]));  

 

Sample code:

 

1. alter table saleBill add(constraint saleBillFk foreign key(saleBookBillId) references saleBookBIll(id));  

 

(3) Delete Reference Relations between Tables by Using SQL Statements

 

1. alter table tableName drop constraint constraintName;  

 

Sample code:

 

1. alter table saleBill drop constraint saleBillFk;