Data integrity is divided into the following 3 categories.
(1) Domain integrity: refers to the input validity of a column, whether it is allowed to be null value. Domain integrity is enforced by limiting the type (by setting the column's data type), format (by CHECK constraints and rules), or by the range of possible values (by FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules). For example, students' test scores must be between 0~100, and the gender can only be "male" or "female".
(2) Entity integrity: refers to ensuring that all rows in the table are unique. Entity integrity requires that all rows in the table have a unique identifier. This unique identifier may be a column or a combination of several columns, called the primary key. That is, the primary key in the table must take a unique value on all rows. Methods to enforce entity integrity are: indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY attributes. For example, the value of SNO (student number) in the student table must be unique, it uniquely identifies the student represented by the corresponding record, and the duplication of the student number is illegal. The student's name cannot be used as the primary key, because it is entirely possible for two students to have the same name and surname.
(3) Referential integrity: It refers to ensuring the referential relationship between the main keyword (cited table) and the external keyword (citation table). It involves consistency maintenance for two or more table data. The foreign key value associates the records in the reference table that contain this key with the records in the referenced table that match the primary key to the foreign key. When entering, changing, or deleting records, the defined relationships between the tables are maintained with reference to integrity, ensuring that the key-values are consistent across all tables. This consistency requires that non-existent values are not referenced, and if a key-value changes, all references to that key-value are changed consistently throughout the database. Referential integrity is based on the relationship between the foreign key and the primary key. For example, the course number of the course of the student learning course must be a valid course number, and the foreign key CNO (course number) of the score table (grade table) will refer to the primary key CNO (course number) in the course table (course schedule) to achieve data integrity.
Domain integrity, entity integrity, and referential integrity are implemented on columns, rows, and tables, respectively. Data integrity can be implemented at any time, but when implementing data integrity on a table with existing data, the system must first check whether the data in the table meets the implementation integrity, and only if the data in the table meets the implementation integrity, the data integrity can be successfully implemented. |
Posted on 4/16/2015 6:09:57 PM
|
|
|
