The restraint mechanism of INNODB

Data integrity

A difference between relational database systems and file systems is that the relational database itself guarantees the integrity of the stored data and does not require application control, and file systems generally need to be controlled at the terminal. Almost all relational databases provide a constraint (constraint) mechanism, and constraints provide a powerful and easy way to ensure data integrity in a database, with three forms of data integrity:

1. There is a primary key in the Entity Integrity guarantee table. In the InnoDB storage engine table, we can guarantee the integrity of the entity by defining the primary key or the unique key constraint. Or we can also write a trigger to ensure data integrity.
2. Domain integrity guarantees that the value of the data satisfies a specific condition. In the InnoDB Storage engine table, domain integrity can be ensured by selecting the appropriate data type to ensure that a data value satisfies a specific condition, a foreign key (Foreign key) constraint, a trigger is written, and a default constraint is considered as an aspect of enforcing domain integrity.
3. Referential integrity guarantees the relationship between two tables. The InnoDB storage engine supports foreign keys, allowing users to define foreign keys to enforce referential integrity, or by writing triggers to enforce them. For the InnoDB storage engine, 4 constraints are provided:Primary key,Unique key,Foreign key,Default,notNULL

Creation and lookup of constraints

For the creation of a constraint, it can be defined when the table is established, or it can be created later using the ALTER TABLE command. For a unique key constraint, we can also create a unique index. For primary KEY constraints, the default constraint name is primary key. For a unique key constraint, the default constraint name is the same as the column name, and of course you can specify a name artificially. For the foreign key constraint, there seems to be a cryptic default name. Here is a simple statement to create a table with a primary key and a unique key:

CREATE TABLE U (ID int,name varchar), Id_card char, primary key (ID), unique key (name));

Select constraint_name,constraint_type from information_schema. table_constraints where table_schema= ' mytest ' and table_name= ' u ' \g;

1.row***************************

Constraint_name:primary

Constraint_type:primary KEY

2.row***************************

Constraint_name:name

Constraint_type:unique

Of course we can also create by ALTER TABLE, and we can define the name of the constraint, such as:

ALTER TABLE u add unique key Uk_id_card (Id_card),

Select Constraint_name,constraint_type from INFORMATION_SCHEMA. table_constraints where table_schema= ' mytest ' and table_name= ' U ' \g;

Then we'll look at the constraints of foreign key, so we have to create another table:

CREATE table P (ID int,u_id int,primary key (ID), foreign key (u_id) references P (ID));

Select Constraint_name,constraint_type from INFORMATION_SCHEMA. table_constraints where table_schema= ' mytest ' and table_name= ' U ' \g;

1.row***************************

Constraint_name:primary

Constraint_type:primary KEY

2.row***************************

Constraint_name:p_ibfk_1

Constraint_type:foreign KEY

Here we see all the constraints under the current MySQL library through the table table_constraints under the INFORMATION_SCHEMA schema. For the definition of FOREIGN key constraint, we can also view the table referential_constraints, and we can understand the properties of the foreign key in detail, such as:

SELECT * FROM INFORMATION_SCHEMA. referential_constraints where constraint_schema= ' mytest ' \g;

1.row***************************

Constraint_catalog:null

Constraint_schema:test2

Constraint_name:p_ibfk_1

Unique_constraint_catalog:null

Unique_constraint_schema:test2

Unique_constraint_name:primary

Match_option:none

Update_rule:restrict

Delete_rule:restrict

Table_name:p

Referenced_table_name:p

Differences in constraints and indexes

We have seen the constraints of primary key and unique key. Some people can not help asking, this is not the way we create an index? What is the difference between a constraint and an index? Indeed, when you create a unique index, you create a unique constraint. However, the concept of constraints and indexes is still different, the constraint is a logical concept to ensure the integrity of the data, and the index is a data structure , a logical concept, in the database is more a physical storage way.

Constraints On Error data

By default, the MySQL database allows the insertion or updating of illegal or incorrect data, or internally converting it to a valid value, such as inserting a null value for a field that is not null, changing it to 0 and inserting it, so that it does not constrain the correctness of the data itself.

Let's look at an example:

CREATE table A (id int not null,date date not null);

Insert into a select NULL, ' 2009-02-30 ';

Show warnings;

1.row***************************

Level:warning

code:1048

Message:column ' ID ' cannot be null

2.row***************************

Level:warning

code:1265

Message:data truncated for column ' Date ' at row 1

SELECT * from A;

+----+-------------+

|id|date

|0|0000-00-00

+----+-------------+

For columns with NOT NULL I inserted a null value and inserted an illegal date ' 2009-02-30 ', MySQL did not error, but showed a warning (warning). If we want to constrain the insertion or update of illegal data,MySQL is to prompt for an error rather than a warning, then we should set the parameter Sql_mode to strictly audit the input parameters , such as:

Set sql_mode= ' Strict_trans_tables ';

Insert into a select NULL, ' 2009-02-30 ';

ERROR 1048 (23000): Column ' id ' cannot be null

Insert into a Select 1, ' 2009-02-30 ';

ERROR 1292 (22007): Incorrect date value: ' 2009-02-30 ' for column ' date ' at row 1

Our goal is to achieve this time, MySQL has constrained the legitimacy of the input values, and for different errors, the prompt error content is also different. Parameter Sql_mode can be set to a number of values, please refer to the official MySQL documentation.

Enum and set constraints

MySQL does not support traditional check constraints, but some of these constraints need to be addressed through enum and set types. If there is a gender type on our table, the scope of the specified domain can only be male or female, in which case we can constrain by the enum type:

CREATE TABLE A (ID int,sex enum (' Male ', ' female '));

Insert into a Select 1, ' Female ';

Insert into a select 2, ' Bi ';

Records:1 duplicates:0 warnings:1

As you can see, the insertion of the second record still holds a warning. Therefore, if you want to implement a check constraint, you also need to set the parameter Sql_mode:

SET sql_mode= ' strict_trans_tables ';

Insert into a select 2, ' Bi ';

ERROR 1265 (01000): Data truncated for column ' sex ' at row 1

This time the illegal input values are constrained, but limited to discrete numerical constraints , for the traditional check constraints supported by the continuous value of the range constraints or more complex constraints, enum and set type is powerless, then we need to implement the constraint through a trigger.

Triggers and constraints

Integrity constraints can also be implemented using triggers that automatically invoke SQL commands or stored procedures before or after the insert, delete, and update commands . MySQL 5.0 implementation of the trigger is not very complete, the limit is more, and from the beginning of MySQL 5.1, the trigger has been relatively stable, the function has been significantly improved.

The command that creates the trigger is create TRIGGER, and only a MySQL user with super privileges can execute this command:

CREATE

[definer={user| Current_User}]

TRIGGER trigger_name

before| After insert| update| DELETE

On Tbl_name

For each ROW trigger_stmt

you can create up to 5 triggers for a table , that is, the before of INSERT, UPDATE, delete, and after each. Before and after represent the time at which the trigger occurs, whether it occurs before or after each row of operations. currently MySQL only supports the trigger mode for each row, which is triggered on a per-line record , and does not support the triggering of a For each statement, such as DB2.

With triggers, we can implement features that are not natively supported by the MySQL database, such as support for traditional check constraints , materialized views , advanced replication , auditing , and so on. Here we first focus on the trigger's support for constraints.

We consider the user's consumption chart, each time the user buys the same item after the amount is reduced, if there is a malicious person to do a similar minus a negative operation, so that the user's money will continue to increase.

CREATE TABLE Usercash (userid int,cash int unsigned not NULL);

Insert into Usercash select 1,1000;

Update Usercash set cash=cash-( -20) where userid=1;

For the database, the above content does not have any problems, can be normal operation, no error. But from a business logic, this is wrong, and consumption should always subtract a positive value instead of a negative value. So if the logic behavior is constrained by a trigger, you can do the following:

CREATE TABLE Usercash_err_log (

UserID int NOT NULL,

Old_cash int unsigned NOT NULL,

New_cash int unsigned NOT NULL,

User varchar (30),

Time datetime);

delimiter$$

Create trigger tgr_usercash_update before update on Usercash

For each row

Begin

If New.cash-old.cash>0 Then

Insert INTO Usercash_err_log Select Old.userid,old.cash,new.cash,user (), now ();

Set New.cash=old.cash;

End If;

End

$$

Delete from Usercash;

Insert into Usercash select 1,1000;

Update Usercash set cash=cash-( -20) where userid=1;

SELECT * from Usercash;

+--------+-------+

|userid|cash

|1|1000

+--------+-------+

SELECT * from Usercash_err_log;

+--------+------------+------------+-------------------

|userid|old_cash|new_cash|user|time

|1|1000|1020| [Email protected]|2009-11-06 11:49:49

+--------+------------+------------+------------------

We created a table to record the error value of the log, first to determine the difference between the old and new values, normally consumption is always reduced, so the new value should always be less than the original value, so for more than the original value of the data, we judged to be illegal input, the cash value is set to the original value.

FOREIGN key

Foreign keys are used to guarantee referential integrity, and the MySQL default MyISAM storage engine does not natively support foreign keys, and the definition of a foreign key only serves as a comment. The InnoDB storage engine fully supports foreign key constraints. The foreign key is defined as follows:

[Constraint[symbol]] FOREIGN KEY

[Index_name] (Index_col_name,......)

REFERENCES tbl_name (Index_col_name,......)

[on DELETE Reference_option]

[on UPDATE Reference_option]

Reference_option:

Restrict| cascade| SET Null|no ACTION

We can add a foreign key when we create a table, or it can be added by the ALTER TABLE command after it is created.

An example of creating a simple foreign key is as follows:

CREATE TABLE Parent (

ID INT not NULL,

PRIMARY KEY (ID)

) Engine=innodb;

CREATE TABLE Child (

ID INT,

parent_id INT,

Index Par_ind (parent_id),

FOREIGN KEY (parent_id) REFERENCES parent (ID)

) Engine=innodb;

In general, we call the referenced table the parent table , and the other referenced table as the child table. The foreign key is defined as,on Delete, and on update to represent the actions of the child table when the parent table does the delete and update operations . The child table operations that can be defined are:

1. CASCADE: When a delete or update operation occurs on the parent table, the data in the corresponding child table is also deleted or update.
2. SET null: The data in the corresponding child table is updated to a null value when a delete or update operation occurs on the parent table. Of course, the corresponding column in the child table must allow null values.
3. No action: Throws an error when the parent table has a delete or update operation, and does not allow this type of operation to occur.
4. RESTRICT: When a delete or update operation occurs on the parent table, an error is thrown that does not allow such an operation to occur. If you do not specify on delete or on UPDATE when you define a foreign key, this is the default foreign key setting. In Oracle, there is a foreign key constraint called delay checking (deferred check), and the current MySQL constraint is an immediate check (immediate check) , so as you can see from the above definition, no in MySQL database The action and restrict functions are the same.

in the Oracle database, the foreign key is often overlooked, for the column that establishes the foreign key, must not forget to add an index to this column . the InnoDB storage engine automatically adds an index to the column when the foreign key is established , as does the Microsoft SQL Server database. Therefore, it is good to avoid the generation of deadlock problems caused by no indexes on foreign key columns.

For referential integrity constraints, foreign keys can play a very good role. However , for the import of data, foreign keys often lead to a large amount of time spent on foreign key constraint checks, because the external keys of MySQL is checked immediately , so the import of each row will be a foreign key check. But we can ignore foreign key checks during the import process, such as:

SET foreign_key_checks=0;

LOAD DATA ...

SET Foreign_key_checks=1;

The restraint mechanism of INNODB