Database Review 3--Database integrity

Database Review CH5 Integrity 5.1 integrity constraints

Database Integrity refers to the logical consistency, correctness, validity and compatibility of the data in the database, then the integrity constraint refers to the user insert, modify and delete operations, the DBMS in order to ensure the database logical consistency, correctness, Constraints required to check for validity and compatibility

C.j.date describes four integrity constraints in the book "an Introduction to Database Systems":

• Type constraints
• attribute constraints
• Relationship variable constraints
• Database constraints

C.j.date's theory is mostly based on his self-created tutorial D, the conceptual database operating language, the following description is tutorial D, note not to be confused with SQL

(1) Type constraints

A type constraint is a constraint on the kind (or number of fields ) of a custom type and the size of a value , for example, if our custom type weight is a real type and requires that weight must be greater than 0, define a type constraint with tutorial D as follows:

POSSREP(RATIONAL)the_weight(weight) > 0.0;

Possrep is possible representation (possible expression), rational is the rational number, the_ Add weight (mentioned earlier this is the c.j.date of the type of operation symbol, representing the value) weight action on the weight representation value

(2) attribute constraints

A property constraint is an implicit constraint that defines the type of a property defined when the relationship is defined, and Tutorial D defines a relationship as follows:

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

For example, the status of the previous example requires implicit adherence to type-integer constraints

(3) Relationship variable constraints

A relationship variable constraint is a constraint on a tuple in a relationship, such as a supplier's status in London must be 20:

where‘London‘and20);

Relationship variable constraints are always checked immediately

(4) Database constraints

The constraint of a relationship variable is a constraint on the internal properties of a single relational variable (which can be understood as a relationship/table), and if the constraint involves more than one relationship variable, it is called a database constraint

In the case of database constraints, the number of qualified parts vendors must be equal to the number of parts in the supply table:

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

(5) The golden Rule of constraint design (golden rules)

About Golden Rule,c.j.date What we want to express here is that all of the integrity constraints we define are called internal constraints , and the user and DBMS are clear, such as weight must be greater than 0

However, the system we designed has many other external constraints , such as my weight is 69.5, so I can insert a tuple (Jcguo, 69.5), but insert (Jcguo, 100.5) said I was a big fat DBMS is also allowed, But it's against the real-world constraints.

This session relates to the correctness that was previously included in the definition of completeness, and c.j.date that the integrity of this part should be defined by the user or DBA outside the database, rather than being handed over to the DBMS

5.2 Keys Key

A special integrity constraint can be defined in the database-key (key, or sometimes translated as code, I call the key to be more smooth)

(1) Definitions of various keys

Let's start by figuring out the definitions of the various keys:

• Super key: A hyperlink that uniquely identifies a tuple's set of properties in a relationship
• Candidate key: Candidate key (usually called candidate Code), no extra attributes/non-protocol super-keys
• Primary key: Primary key, user selected as a candidate key for unique Table tuple, primary key cannot be null
• Alternate key: Optional key, candidate key except primary key , no candidate key selected by user
• FOREIGN key: Foreign key, which is dependent on other relationships to ensure the logical integrity of the database constraints

The first four keys are very well understood, the following emphasis on understanding the foreign key

(2) Foreign key

The relationship R1 declaration creates a foreign key FK on the attribute A1, then there are:

• This foreign key FK must be reference (referenced or since) in the candidate key of another relationship R2 CK (general reference the primary key of another relationship, set CK created on attribute A2)
• The function of this reference is to ensure that the values that appear in the attribute A1 in the R1 are all present in the A2 of R2 (Integrity constraints)

Note that foreign key FK does not require that the values appearing in attribute A2 (CK) in the R2 must appear in the attribute A1 in the R1, except that the unilateral constraint: once appearing in FK, it must appear in CK

The foreign key has another most important feature: The referential action (associated action), which is an implicit special trigger (the set of actions that will depart whenever a departure condition is met)

The following is a detailed description of the associated actions supported by SQL when speaking SQL integrity

5.3 SQL Integrity

Once again, all of the completeness of the previous review is abstract and needs to be understood, so this section of SQL integrity needs to be remembered.

(1) Domain constraints

Domain Constraints is a constraint on the value range of a property in SQL, and the SQL Declaration domain constraint is done at CREATE TABLE, supporting explicit constraint declarations with names, and convenient constraint declarations without names in two ways:

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

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 the common comparison operation symbol in a check clause, or you can use in to construct a compound statement

Naming the constraints for some special cases we need to modify the constraints, see my other blog post:

In addition, SQL supports adding constraints to declared table, using alter table statements, this course does not require

(2) Primary key

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

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

Equivalent to:

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

It is important to note that the primary key cannot be empty and cannot insert a tuple that does not contain a primary key/null for the primary key

Primary key can also be used for multiple properties, as mentioned in the previous concept, how to design according to requirements

(3) Unique

Implementing candidate codes in SQL using unique constraints, a relationship can have multiple unique constraints

This sentence translated from the teacher Ppt, personally feel the need to add:

• The concept of candidate key is watered down in SQL
• The unique constraint not only implements the candidate code, but also the super-code, that is, it has no irreducible limitations

Declaring a Uniqle constraint is similar to a primary key, for example

(4) Foreign key

Foreign key before leaving a pit, here is a direct example:

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

First, create a table dept is the relationship of the system, its primary key is d#, and then create a student table, its properties d# on the declaration of a Reference 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 declaration is equivalent to:

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#));

The constraint of the foreign key is to restrict the students in the table d# must appear in the d# of the system, then it is easy to think of a problem: if USTC new headmaster Van Lou The 11 series, that is, the list of d# for the ' 011 ' tuple deleted words, So our 11-series students in the student table tuples not be satisfied with the foreign key constraints?

In this time, we need to introduce the associated action, which can be divided into Cascade (Cascade) action and set action.

1. Cascade Action

level linkage is a series of primary key-foreign key-primary key-foreign key-... A constraint that references a primary key in the back end of the chain delete or update, which produces (automatic, triggers) a series of foreign key delete or update operations along the reference chain from a backward forward

For example, we declare a d# delete cascade, i.e.:

create   Table  Student (s# char  (10 ) primary  key , name char  ( Span class= "Hljs-number" >20 ), gender char  (1 ) check  (gender in  (  ' F ' ,  ' M ' ), d# char< /span> (3 ) foreign   Key  references  Dept (d#) on  delete  cascade );  

When the 11 series is deleted, all students in the 11 series are deleted because the student table refers to the primary key of the Dept table by the foreign key, as defined by the level linkage.

Then assume that the Dept table refers to a school number u# foreign key, it is a university table of the primary key, at this time constitute a reference chain, if the university table deleted USTC entries, then according to the level linkage as defined USTC 11 series, All students in the 11 series will be automatically deleted.

Cascade action can also be on update cascade , for example, 11 series Alias 1 series, then 11 students can automatically change d# to ' 001 '

The DBMS handles cascade actions according to transactions , and if the linkage of one part of the chain is violated by other integrity constraints, the entire modification will be rolled back (the concept of the transaction), i.e. the modification fails

The PPT reads:

Referential integrity is only checked at the end of a transaction
––intermediate steps is allowed to violate referential integrity provided later steps remove the violation, otherwise it Would is impossible to create some database states, e.g. insert, tuples whose foreign

Unfortunately in the experiment I found this rule MySQL is applicable but Oracle does not apply (at least in PL/SQL)

SQL supports one foreign key at a time with a drop cascade and an update cascade

2. Set the action

Setting the action is good to understand:

• On delete Set NULL
• On Delete Set default

5.4 Assertion and trigger (1) Assertion

SQL also supports the use of assertions (assertion) to define the conditions that a database must meet outside a relationship, with the syntax:

create assertion <assertion_name> check <predicate>;

Assertion is somewhat similar to the database constraints defined by C.j.date, which can be used for single-or multiple-table

A complex example of PPT that limits the sum of the wages of each department must be less than the sum of the wages 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: How do I think the first one is to be added in the back d# ~= ‘d1‘ and ?

(2) Trigger

Triggers define a series of actions that need to be performed automatically when the database state changes (tuple modifications), and the definition triggers include two points:

• Trigger conditions
• Perform actions

Triggers also have several important properties:

• Pre-trigger or post-trigger
• Line trigger or overall trigger

Pre-trigger or post-trigger better understanding, line trigger or overall trigger refers to an update or delete when multiple tuple modifications are involved, whether the whole is triggered once or if each tuple is triggered once

As an example, here is the row trigger I created on Oracle:

create  or  replace  trigger  Countstud after  delete  or  insert  or  update  on  Studfor  each  row  begin  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 to count the number of people in the Department (or a trigger called the integrity of the number in the system), which points out:

• after delete or insert or update on StudSpecifies the trigger's condition and trigger time: triggered when the stud table has a delete, insert, or update operation
• for each rowDeclares that this is a row trigger, triggered by a row
• The action of the trigger is specified in the Begin and end package body

As for the :new and are :old reserved variables, corresponding to delete, insert or update three operations they have the following meanings:

meaning	Insert	Update	Delete
: New	New inserted tuple	Updated tuples	Null
: Old	Null	Pre-update tuples	Deleted tuples

Database Review 3--Database integrity