MySQL database Advanced (i)--Data integrity

MySQL database Advanced (i)--data integrity first, data integrity Introduction 1, Data integrity Introduction

Data redundancy refers to the existence of some duplicate data in the database, data integrity refers to the data in the database can correctly respond to the actual situation.
Data integrity refers to the reliability and accuracy of data, data integrity types are four kinds:
A, Entity integrity: the integrity of the entity enforces the integrity of the table's identifier column or primary key (through UNIQUE constraints, primary key constraints, or identity column properties).
B, Domain integrity: restriction type (data type), format (by checking constraints and rules), possible range of values (through FOREIGN KEY constraints, check constraints, default value definitions, non-null constraints and rules).
C, referential integrity: Referential integrity preserves defined relationships between tables when deleting and entering records. Referential integrity ensures that key values are consistent across all tables and cannot reference values that do not exist. If a key.
D, Custom integrity: User-defined business rules, such as using triggers to implement custom business rules.

2. How to implement data integrity

MySQL does not support check constraints, although you can add a check constraint on a column, but it does not work.

II. Entity Integrity Implementation 1, Entity integrity Implementation Introduction

There are two ways to implement entity integrity:
A, PRIMARY KEY constraint: A table can have only one column to set the primary key, the value must be unique, not allowed to be empty, InnoDB storage engine, the primary key is the index.
B, unique value constraint: A table can have more than one column to add a unique value constraint and always allow a record to be null.
Entity integrity, implemented by primary key and uniqueness constraints, to ensure that records in a table have a unique list of identities. The primary key is divided into PRIMARY key and auto_increment PRIMARY key two kinds.

2. Primary key

MySQL's primary key name is always primary, and when a primary key constraint is created, if the table's storage engine is InnoDB, the system defaults to establishing a unique index on the columns column combination on which it resides.
A PRIMARY KEY constraint is equivalent to a combination of a unique constraint and a non-null constraint, a PRIMARY KEY constraint column does not allow duplicates, a null value is not allowed, a PRIMARY KEY constraint for a multi-column combination, the column is not allowed to be null, and the combined value does not allow repetition. Only one primary key is allowed per table, and a PRIMARY key constraint can be created at the column level or at the table level.
A, specifying A primary key when creating A table
How to specify a primary key when creating a table one:

create table product ( productID int PRIMARY KEY, pName VARCHAR(10), price DOUBLE )ENGINE=MyISAM default CHARSET=utf8;

How to specify a primary key when creating a table two:

create table product ( productID int, pName VARCHAR(10), price DOUBLE, CONSTRAINT pk_s_productID PRIMARY KEY(productID) )ENGINE=MyISAM default CHARSET=utf8;

When you insert a record in a table that specifies a primary key, you are not allowed to insert a duplicate ID, and if you do not specify a value for the primary key, the default is 0.
The storage engine of the MyISAM type does not create an index on the primary key column, and the records in the table are stored in the same order as the insertion order.

The InnoDB storage engine automatically creates an index on the primary key column, and the inserted records are emitted according to the order of the primary key values.
alter table product ENGINE=InnoDB;

B, increase the primary key
alter table TStudent add primary key(studentid);
C. Remove the primary key
alter table TStudent drop primary key;

3. Self-increment primary key

AUTO_INCREMENT PRIMARY KEY
If you do not specify a primary key value, you automatically increase the maximum value of the existing primary key value by 1 automatically as the primary key for the new record, and the primary key value starts at 1. You can add a self-increment primary key on a column of the data data type Integer.

A. Specifying self-increment columns when creating tables

create table product ( productID int PRIMARY KEY AUTO_INCREMENT not NULL, pName VARCHAR(10), price DOUBLE )ENGINE=MyISAM default CHARSET=utf8;

B. Specify the self-increment column for an existing table
alter table TStudent modify column studentID int PRIMARY KEY AUTO_INCREMENT;

C, delete the self-increment column in the table
alter table TStudent modify column studentID int not NULL;
Delete self-increment column, still primary key, but no self-growth function

4. Composite PRIMARY Key

Use two or more columns of a table to create a primary key.
A, specifying A Composite primary key when creating A table

create table student ( studentID int, id INT, sname VARCHAR(10), score int, PRIMARY KEY(studentid,id) )ENGINE=MyISAM default CHARSET=utf8;

B. Add composite primary key to table
alter table student add PRIMARY KEY(sudentID,id);
C. Remove the composite primary key
alter table student drop PRIMARY KEY;

5. Unique Constraint

Unique KEY, single constraint, specifies that data for a column and a combination of columns cannot be duplicated.
A. Specifying uniqueness constraints when creating tables

create table score (sname VARCHAR(10) UNIQUE, score int not NULL );

B. Add uniqueness constraints to existing columns
alter table score add CONSTRAINT uc_sname UNIQUE(sname);
If there are duplicate values for existing records in the table, the addition of uniqueness constraints is not allowed. You can find records with duplicate values, delete them, and create uniqueness constraints by aggregating functions.
C. Create a compound uniqueness constraint

create table student ( studentID int, id INT, sname VARCHAR(10), score int, CONSTRAINT uc_id UNIQUE(studentID, id) )ENGINE=MyISAM default CHARSET=utf8;

D. Remove the uniqueness constraint for a column
alter table score drop index uc_sname;

Third, domain integrity 1, default value

When you insert a new record in a table, the database system automatically assigns a default value to the field if no value is assigned to the field.

create table st(sid INT not null primary key auto_increment,sname varchar(10),subject varchar(20) default ‘软件工程‘,entertime TIMESTAMP default now());

To add a default value constraint to a column in a table:
alter table st modify column subject VARCHAR(20) default ‘计算机科学与技术‘;
To delete a default value constraint for a column in a table:
alter table st modify column subject VARCHAR(20) default NULL;

2. Create a non-null constraint

A non-null constraint is used to ensure that the value of the top column is not NULL, and the non-null constraint can only appear on the columns of the Table object.
Null type characteristics: All types of values can be null, including int, float, and other data types an empty string is not equal to null,0 and not equal to NULL.
A, specifying A non-null constraint for A column when creating A table

create table score (sname VARCHAR(10) not NULL, score int not NULL );

B. Specify a non-null constraint for the specified column
alter table score modify column score int not NULL;
C. Remove non-null constraints
alter table score modify column score int;

3. Checking Check

The check keyword, which does not work when inserting a new row or changing an existing row, prevents a value that does not meet the criteria from entering the column and invalidates the null value because inserting NULL is equivalent to not inserting. A column can have multiple check.
Age Int. check (age between and 20);
Currently MySQL does not support check constraints, and Microsoft MSSQL supports check constraints, but you can specify a check constraint when creating a table, but it does not work.

Iv. referential Integrity 1, Introduction to referential integrity

MySQL referential integrity is typically achieved through the MySQL foreign key (foreign key).
A column for a table referenced by a foreign key (supported only by InnoDB) must be a primary key.
A foreign key declaration consists of three parts:
A, which column or column combination is a foreign key
B. Specify the tables and columns referenced by the foreign key
C, reference action [Cascade (Cascade operation), restrict (Reject action), set NULL (set to NULL), no action,set default].
If a FOREIGN KEY constraint specifies a referential action, the primary table record is modified, deleted, the columns referenced from the table are modified, or not modified, rejected or set to the default value.
The column name of the reference table must be a primary key, and you must delete the reference relationship or delete the current table when you delete the reference table.

2. Specify a foreign key when creating a table

Create two tables, student table student and score table score, the value of the SID column of the score table refers to the student table (the SID column of the student table student is set as the primary key, and the storage engine for the table is InnoDB, and the storage engine for the score table score must also be set to InnoDB).

create table student(sid int not null primary key,sname varchar(20)) engine=innodb;create table score(sid int not null,mark INT,constraint score_fk FOREIGN KEY (sid)references student(sid) on delete cascade on update cascade) engine=innodb;

Insert a record in the student table
insert into student values (1,‘孙悟空‘)
Insert a record in the score table, the study number is 1, success.
insert into score values (1,98)
Insert a record in the score table, the study number is 2, failed.
insert into score values (2,88)
In the Student table insert the number is a record of 2
insert into student values (2,‘唐僧‘)
Again in the score table insert a school number is 2 record, success, prove foreign key reference success.
insert into score values (2,88);

3. Delete the reference constraint

ALTER TABLEScore DROP FOREIGN KEY SCORE_FK;

4. Add referential constraints to existing tables

ALTER TABLEScore ADD CONSTRAINT score_fk2 FOREIGN KEY ( sid ) REFERENCES student ( SID);

5. Verification level linkage for deletion and update

The referential integrity of the score table creation, the delete action and the reference action of the Update action SELECT CASCADE (Cascade operation), when the SID of the student table is updated, the corresponding SID of the score table score is also updated, and when the student is deleted, the record of the SID corresponding to the score table is also automatically deleted.
The student's school number is 1 and the student's number is 10.
update student set sid=10 where sid=1
View the score table, you can see the previous study number is 1 has become 10
select * from score
Delete Student form number is 2 students
delete from student where sid=2
Can see the score table, the student's grades have been cascade deleted
select * from score

6, verification level linkage for no aaction

Level linkage is set to no action, and if there is a matching record in the child table, the Update/delete action is not allowed for the parent table corresponding to the candidate key.
Restrict action with no action is to check the foreign key constraint immediately.
Set the reference action to no action, and if the score table score has the student SID, you will not be able to change the student Sid column of the Student Table student table, or delete the student. Unless you delete the student's score first, delete the student.
Delete a foreign KEY constraint for a score table
ALTER TABLEScore DROP FOREIGN KEY SCORE_FK;
To increase the SID Column foreign KEY constraint for a score table

ALTER TABLE `score` ADD CONSTRAINT `score_fk` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION;

The update number is 10 of the student's school number, failed
update student set sid=11 where sid=10
Delete Student number is 10 of the students, failed
delete from student where sid=10
You need to delete the record from the student's score table before deleting the student.

delete from student where sid=10delete from score where sid=10;

7. Verification level linkage as set NULL

When Update/delete records on a parent table, set the column of matching records on the child table to null?, note that the foreign key column of the child table cannot be null.
Delete a foreign KEY constraint for a score table
ALTER TABLEScore DROP FOREIGN KEY SCORE_FK;
Increase the SID column foreign KEY constraint for the score table, and the reference action is set NULL
ALTER TABLEScore ADD CONSTRAINT SCORE_FK FOREIGN KEY ( sid ) REFERENCES student ( SID) ON DELETE SET NULL ON UPDATE SET NULL;
Modify the SID column default value of the score table to null

ALTER TABLE `score` MODIFY COLUMN `sid` INTEGER(11) DEFAULT NULL;insert into student values (1,‘孙悟空‘)insert into student values (2,‘猪八戒‘)insert into score values (1,98)insert into score values (2,88)

Delete Student Form School Number 1 students
delete from student where sid=1
View the score table, table number 1 for the column null
select * from score

MySQL database Advanced (i)--Data integrity