Database Integrity Ch.5

Database integrity refers to the correctness (correctness) and compatibility of data (compat-ability)

5.1 Entity Integrity definitions

There are two ways to describe a single attribute code, one is defined as a table constraint, and one is defined as a column-level constraint

Multiple attributes can only be defined as table-level constraints

Entity integrity check and default handling

Check whether the main code is only 1, check the main code of the individual properties are not empty.

A generic database will have an index on the main code.

5.2 Referential Integrity 5.2.1 referential integrity check and default handling

If you want to modify the reference table default, direct rejection
If modifying the referenced table defaults then processing according to the user's settings

5.3 Constraints on user-defined integrity attributes

Non-empty
Only
Checks whether a column value satisfies a condition check statement

Refuse if not satisfied

Constraints on tuples

Check statements can set constraints on tuples

5.4 Integrity constraint naming clause

1 integrity constraint naming sentence name

Constraint < integrity constraint name > < integrity constraints >

Complete new constraints include unique NOT NULL primary key foreign key check, etc.

Easy to complete modification Delete add, etc.

Modify:
ALTER TABLE
Drop constraint C4;

ALTER TABLE Student drop contraint C4;
Alter Tabele student
Add constraint C1 Check (Son between 2313 and 23434)
Delete and add a new constraint first

5.5 Domain Integrity Limitations

Establish a domain and declare a range of values

Create Domin Grenderdomin char (2) Check (value in (' Asas ', ' SDSD ');

Ssex's instructions can be shortened to
Ssex Gerndedomin

Create Domin Genderdomin char (2)
Constraint GD Check (value in ("Nan", "NV"))

Alter domin genderdomin drop constraint GD;

Alter Domin genderdomin add constraint GDD Check (value in (1,0))

5.6 Assertions

An assertion mechanism can be used so that any action that violates an assertion is denied execution
Create assertion < assert name >

A check sentence is similar to a where statement

Create Asssertion asse_sc_cnum2
Check (60>all (SELECT * from * * * where * *))

5.7 Triggers

The trigger is also called
Event condition Action Rule event-conditon-action
Check the conditions of a rule when a particular event occurs

The format of the trigger is
Create TRIGGER < Trigger name >
{before | after} < TRIGGER event > on < table name >
Reference New|old row as < variables >
For each {row |statement}
[When < trigger conditions;] < departure action >

1 only the table has the ability to create triggers, and only a certain number of triggers on a table

2 trigger name must be unique in unified mode

3 triggers must be defined on the base table (the view is not allowed

4 Trigger event can be an insert update delete delete or a combination of these events such as INSERT or update or update of (trigger column,...) Indicates when the columns are modified to trigger

5 Trigger Type: Can be divided into row-level trigger FIR each row statement-level trigger for each statement
An operation that modifies 1000 rows at a time, a statement-level trigger executes 1 times, and a row-level trigger executes 1000 times
6 Trigger Condition: The trigger's contents are executed only when the trigger condition is true

7 Trigger Action Body
can be anonymous the PL/SQL process is fast
You can also call a stored procedure

If it is a row-level trigger, you can use new old to refer to the older values in the site and Updaye Delete after the update insert event
If it's a statement-level trigger, No.

Crate Trigger Sc_u after update
Reference
NewRow as Newtuple
Oldrow as Oldtuple
For each row
When (Newtupel.grde>oldtuple.grade1.1)
Insert into * *
VALUES ("SDASD," ' SDA ')

Statement-level triggers are also newtable and oldtable can be referenced

5.7.2 activating triggers

The following sequence is followed when executing the trigger

Execute before Trigger
SQL statement that executes the activation trigger
Executing after triggers

Multiple before after triggers who first create who executes.

5.7.3 Deleting triggers

Drop trigger < trigger name > on < table name >

Database Integrity Ch.5