Database review 3-database integrity, review database integrity

Database review 3-database integrity, review database integrity
Database review CH5 integrity 5.1 integrity constraints

Database integrityIt refers to the logical consistency, correctness, validity, and compatibility of data in the database.Integrity constraintsIt refers to the constraints that the DBMS must check to ensure logical consistency, correctness, validity, and compatibility of the database during user insert, modify, and delete operations.

C. J. Date describes four integrity constraints in An Introduction to Database Systems:

• Type Constraints
• Attribute Constraints
• Link variable Constraints
• Database Constraints

Most of the theories proposed by C. J. Date are based on his own Tutorial D Concept Database operating language. The following description is based on Tutorial D. Be careful not to confuse it with SQL.

(1) type constraints

The Type constraint is about the type of the custom type (or isNumber Field) AndValue sizeFor example, if weight of the custom type is a real number and weight must be greater than 0, use Tutorial D to define a type constraint as follows:

TYPE weight POSSREP(RATIONAL) CONSTRAINT the_weight(weight) > 0.0;

POSSREP is possible representation (possible expression), RATIONAL is RATIONAL number, the _ plus weight (as mentioned above, this is C. j. operation symbol of the type proposed by Date, indicating the value) weight acts on weight to indicate the value

(2) attribute Constraints

An Attribute constraint is an implicit constraint generated by defining the type of an attribute when defining a link. Tutorial D defines a link as follows:

VAR S BASE RELATION(S# S#, status integer, city char);

For example, the status in the previous example must be implicitly subject to the type constraints of Type integer.

(3) constraint on link Variables

A link variable constraint is a constraint on the element groups in a link. For example, the supplier status in London must be 20:

Constraint sc1 is_empty(S where city = 'London' and status ~= 20);

Link variable constraints are always checked immediately

(4) database Constraints

A relational variable constraint is a constraint on the internal attributes of a single relational variable (relational variables can be understood as relational/table). If a constraint involves multiple relational variables, it is called a database constraint.

The following database constraints indicate that the number of parts suppliers must be equal to the number of parts suppliers in the supply table:

Constraint dbc1 count(SP(p#)) = count(P(p#));

(5) Golden Rule)

About Golden Rule, Here C. J. Date wants to express all the integrity constraints we define.Internal constraintsThe user and DBMS are both clear. For example, the weight must be greater than 0.

However, the system we designed has many otherExternal constraintsFor example, if my weight is 69.5, A tuples (jcguo, 69.5) can be inserted. However, insertion (jcguo, 100.5) means that I am a big fat DBMS and I am allowed, but this violates the constraints of the real world.

This session involvesCorrectnessC. J. Date considers that the integrity of this part should be limited by the user or DBA outside the database, rather than being handed over to the DBMS.

5.2 Key

A special Integrity Constraint can be defined in the database-key (key, or sometimes translated as a code. My name is "key)

(1) definition of various keys

Let's first clarify the definition of various keys:

• Super key: A superkey that uniquely identifies a tuples in a link.Attribute Set
• Candidate key: candidate key (usually called candidate code), excludingRedundant attribute/non-standardOfSuperkey
• Primary key: primary key. The user selects a unique table key.Candidate KeyThe primary key cannot be null.
• Alternate key: Optional keys, candidate keysExcept for primary keys, Not selected by the userCandidate Key
• Foreign key: foreign key, which depends on other relationships to ensure database LogicIntegrityConstraints

The first four keys are well understood. The following describes the Foreign keys.

(2) Foreign key

Link R1 declares that a foreign key FK is created on Attribute a1, so there are:

• This foreign key FK must reference (since reference or) The candidate key CK of another link R2 (generally, the primary key of another link reference, where CK is created on Attribute a2)
• The purpose of this reference is to ensure that all values in attribute a1 of R1 appear in a2 of R2. (integrity constraints)

Note: The foreign key FK does not require that all values in attribute a2 (CK) in R2 must appear in attribute a1 in R1, but only the unilateral constraint: once it appears in FK, it must appear in CK

Foreign keys also have another important feature: referential action, which is an implicit specialTrigger(A set of actions that will depart whenever the departure condition is met)

The following describes in detail the associated actions supported by SQL in SQL integrity.

5.3 SQL integrity

Once again, we emphasize that all the integrity we reviewed previously is an abstract concept and needs to be understood, so the SQL integrity of this section needs to be remembered.

(1) domain Constraints

Domain Constraints is a restriction on the range of attribute values in SQL. SQL statements declare that Domain Constraints are completed at create table, two methods are supported: explicit constraint declarations with names and convenient constraint declarations without names:

create table Student(    S# char(10),    name char(20),    gender char(1),    constraint gc check (gender in ('F', 'M'))  );

It is equivalent to the following:

create table Student(    S# char(10),    name char(20),    gender char(1) check (gender in ('F', 'M')));

You can use common comparison operator symbols in the check clause, or you can use in to construct compound statements.

Naming constraints is to modify constraints in some special cases. For more information, see my other blog post:

In addition, SQL supports adding constraints to declared tables.alter tableStatement, not required for this course

(2) primary key

The SQL primary key Declaration is also declared at create table, and there are two ways to declare the primary key (explicit and convenient, explicit primary keys do not require a name, because one link/table can only have one primary key ):

create table Student(    S# char(10),    name char(20),    gender char(1) check (gender in ('F', 'M')),    primary key (S#));

It is equivalent:

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')));

Note that the primary key cannot be blank and cannot be inserted with a tuple that does not contain the primary key/the primary key is null.

Primary key can also act on multiple attributes. As mentioned in the previous concepts, how to design depends on your needs?

(3) unique

The unique constraint is used to implement candidate codes in SQL. A single link can have multiple unique constraints.

This sentence is translated from the instructor's ppt. I personally think it is necessary to add:

• The concept of candidate key is deprecated in SQL.
• The unique constraint can not only implement candidate code, but also implement supercode, that is, it has no limit

The Declaration of the uniqle constraint is similar to that of the primary key.

(4) Foreign key

Foreign key has left a hole before. Here is an example:

create table Dept(    D# char(3) primary key,    name char(20),    type char(20) check (type in ('engineering', 'science', 'business')));

First, a table Dept is created, and its primary key is D #. Then a Student table is created. Its Attribute D # indicates a reference to Dept (D #) foreign key:

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')),    D# char(3) foreign key references Dept(D#));

The above statement is equivalent:

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')),    D# char(3),    contraint dfk foreign key references Dept(D#));

Here, the foreign key constraint is to restrict the D # in the student table to appear in the D # in the department table, so it is easy to think of a problem: if the new president of USTC has removed the 11 series, that is, the system table has deleted the tuples whose D # Is '011, the tuples in students of our 11 series cannot meet the foreign key constraint in the Student table.

At this time, we need to introduceAssociated ActionAssociated actions are divided into Cascade actions and Set actions.

1. Cascade action

Cascade actionIs a series of primary key-foreign key -... A primary key delete or update bound to the backend of the reference chain will generate (automatic, trigger) delete or update operations on a series of Foreign keys along the reference chain from the back to the back.

For example, we declare a D # Delete cascade, that is:

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')),    D# char(3) foreign key references Dept(D#) on delete cascade);

Delete the 11 series. All 11 series students are deleted according to the cascade action definition, because the Student table references the primary key of the Dept table through the foreign key.

Assume that the Dept table references a foreign key of the School Number U #, which is the primary key of a University table and constitutes a reference chain. If the University table deletes USTC entries, the 11 series of USTC will be automatically deleted according to the cascade action.

Cascade actions can also beon update cascadeFor example, if the 11 series is changed to the 1 series, the 11 series students can automatically change D # To '001'

DBMS processes cascade actionsTransactionsIf the cascade action of a part of the reference chain violates other integrity constraints, the entire modification will be rolled back (the concept of a transaction), that is, the modification will fail.

The ppt says:

Referential integrity is only checked at the end of a transaction
-- Intermediate steps are allowed to violate referential integrity provided later steps remove the violation, otherwise it wocould be used to create some database states, e.g. insert two tuples whose foreign keys point to each other

Unfortunately, in the experiment, I found that this rule applies to MySQL but does not apply to Oracle (at least in PL/SQL)

SQL supports a foreign key with a delete cascade and an update cascade at the same time.

2. Set actions

Setting actions is easy to understand:

• On delete set null
• On delete set default

5.4 assertions and triggers (1) Assertions

SQL also supports the use of Assertion to define the conditions that must be met by the database outside the relational database. Syntax:

create assertion <assertion_name> check <predicate>;

Assertion is somewhat similar to the database constraints defined by C. J. Date. It can be used for single or multiple tables.

In a complex example in the ppt, the total salary of each department must be smaller than the total salary of the d1 Department:

create assertion sac check(    not exists(        select * from EMP e2         where (select sum(sal) from EMP e1 where e1.d# = e2.d#)             >= (select sum(sal) from EMP where d# = 'd1')    ));

PS: I think the first where should be followedd# ~= 'd1' andWhat about it?

(2) triggers

A trigger defines a series of actions that need to be automatically executed when the database status changes (modifying tuples). A trigger includes two key points:

• Trigger Condition
• Execute action

The trigger has several important attributes:

• Trigger beforehand or trigger afterwards
• Row-based or overall-based triggering

It is easier to understand trigger beforehand or trigger afterwards,Line-based triggeringOrOverall triggerIndicates whether to trigger the update or delete operation on multiple tuples or trigger each tuples once.

For example, the row trigger I created on Oracle is as follows:

create or replace trigger countStudafter delete or insert or update on Studfor each rowbegin      update Dept set S_count =  S_count+1 where D# = :new.D#;      update Dept set S_count =  S_count-1 where D# = :old.D#;end;

This is a trigger used to count the number of people in the system (or a trigger called to ensure the integrity of the number of people in the system). The points are described as follows:

• after delete or insert or update on StudSpecify the trigger conditions and trigger time: triggered when the Stud table is deleted, inserted, or updated.
• for each rowDeclare that this is a row trigger, triggered by rows
• The action of the trigger is specified in the begin and end packages.

For:newAnd yes:oldThe reserved variables correspond to three operations: delete, insert, or update. They have the following meanings:

Description	Insert	Update	Delete
: New	Newly inserted tuples	Updated tuples	Null
: Old	Null	Pre-update tuples	Deleted tuples