In-depth analysis of MySQL from deleting the database to running away_Advanced (I) - Data Integrity

1. Introduction to Data Integrity

1. Introduction to Data Integrity

Data redundancy refers to the existence of some duplicate data in the database, and data integrity means that the data in the database can correctly reflect the actual situation.

Data integrity refers to the reliability and accuracy of data. There are four types of data integrity:

A. Entity integrity: Entity integrity enforces the integrity of the identifier column or primary key of a table (through a unique constraint, primary key constraint, or identity column attributes).

B. Domain integrity: restricting types (data types), formats (through check constraints and rules), and possible value ranges (through foreign key constraints, check constraints, default value definitions, non-null constraints and rules).

C. Referential integrity: Referential integrity maintains the defined relationships between tables when deleting and entering records. Referential integrity ensures that key values ​​are consistent across all tables and cannot reference nonexistent values. If a key.

D. Definition integrity: Business rules defined by users themselves, such as using triggers to implement custom business rules.

2. Data integrity implementation method

MySQL does not support Check constraints. Although you can add a check constraint to a column, it will not work.

2. Entity Integrity Implementation

1. Introduction to the implementation of entity integrity

There are two ways to implement entity integrity:

A. Primary key constraint: A table can only have one column with a primary key. The value must be unique and cannot be empty. For the innoDB storage engine, the primary key is the index.

B. Unique value constraint: A table can have multiple columns with unique value constraints added to them, always allowing a record to have a null value.

Entity integrity is achieved by primary key and unique constraints, ensuring that records in the table have a unique identifier. The primary key is divided into two types: Primary key and AUTO_INCREMENT PRIMARY KEY.

2. Primary key

The name of the primary key in MySQL is always PRIMARY. When creating a primary key constraint, if the storage engine of the table is innoDB, the system will create a corresponding unique index on the column and column combination by default.

The primary key constraint is equivalent to the combination of the unique constraint and the not-null constraint. The primary key constraint columns are not allowed to be repeated, and null values ​​are not allowed to appear. For the primary key constraint of multiple columns, no columns are allowed to have null values, and the combined values ​​are not allowed to be repeated. Each table is allowed to have at most one primary key. The primary key constraint can be created at the column level or at the table level.

A. Specify the primary key when creating a table

Method 1 to specify the primary key when creating a table:

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

Method 2 to specify the primary key when creating a table:

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

When inserting records into a table with a specified primary key, duplicate IDs are not allowed. If the primary key value is not specified, the default is 0.

The MylSAM storage engine does not create indexes on the primary key columns, and the storage order of records in the table is the same as the insertion order.

The InnoDB storage engine will automatically create an index on the primary key column, and the inserted records will be arranged in the order of the primary key values.

alter table product ENGINE=InnoDB; 

B. Add primary key

alter table TStudent add primary key(studentid);

C. Delete the primary key

alter table TStudent drop primary key;

3. Auto-increment primary key

AUTO_INCREMENT PRIMARY KEY

If you do not specify a primary key value, the maximum value of the existing primary key value will be automatically increased by 1 as the primary key of the new record. The primary key value starts at 1 by default. You can add an auto-increment primary key to a column whose data type is integer.

A. Specify auto-increment columns when creating a table

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

B. Specify an auto-increment column for an existing table

alter table TStudent modify column studentID int PRIMARY KEY AUTO_INCREMENT;

C. Delete the auto-increment column in the table

alter table TStudent modify column studentID int not NULL;

Delete the auto-increment column, it is still the primary key, but without the auto-increment function

4. Composite primary key

Create a primary key using two or more columns of a table.

A. Specify 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 a composite primary key to the table

alter table student add PRIMARY KEY(studentID,id);

C. Delete composite primary key

alter table student drop PRIMARY KEY;

5. Unique constraint

UNIQUE KEY, a unique constraint, specifies that the data in a certain column or a combination of columns cannot be repeated.

A. Specify a unique constraint when creating a table

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

B. Add a unique constraint to an existing column

alter table score add CONSTRAINT us_sname UNIQUE(sname);

It is not allowed to add a unique constraint if there are duplicate values ​​in the existing records in the table. You can use aggregate functions to find duplicate records, delete them, and then create a unique constraint.

C. Create a composite unique index

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

D. Delete the unique constraint of the column

alter table score drop index uc_sname;

3. Domain Integrity

1. Default Values

When inserting a new record into a table, if no value is assigned to the field, the database system will automatically assign a default value to the field.

create table st
(sid INT not null primary key auto_increment,
sname varchar(10),
subject varchar(20) default 'Software Engineering',
entertime TIMESTAMP default now()
);

To add a default value constraint to a column in a table:

alert table st modify column subject VARCHAR(20) default 'Computer Science and Technology';

To remove the default value constraint from a column in a table:

alert table st modify column subject VARCHAR(20) default NULL;

2. Create a non-null constraint

The non-null constraint is used to ensure that the value of the current column is not null. The non-null constraint can only appear on the column of the table object.

Null type characteristics: All types of values ​​can be null, including int, float and other data types. Empty strings are not equal to NULL, and 0 is not equal to NULL.

A. Specify a non-null constraint for the 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

alert table score modify column score int not NULL;

C. Delete the non-null constraint

alter table score modify column score int;

3. Check

The check keyword only works when inserting new rows or changing existing rows. It prevents values ​​that do not meet the conditions from entering the column. It is invalid for null values ​​because inserting null is equivalent to not inserting. A column can have multiple checks.

age int check(age between 10 and 20);

Currently, MySQL does not support check constraints. Microsoft MSSQL supports check constraints, but you can specify check constraints when creating a table, but they do not work.

IV. Referential Integrity

1. Introduction to referential integrity

MySQL referential integrity is generally achieved through MySQL foreign keys.

The columns of the table referenced by the foreign key (supported only by innoDB) must be the primary key.

A foreign key declaration consists of three parts:

A. Which column or column combination is the foreign key

B. Specify the table and column referenced by the foreign key

C. Reference actions [cascade (cascade operation), restrict (reject operation), set null (set to empty), no action, set default].

If a foreign key constraint specifies a reference action, when the primary table record is modified or deleted, the columns referenced by the secondary table will be modified accordingly, or not modified, refused to be modified, or set to the default value.

The column name of the referenced table must be the primary key, and when deleting the referenced table, the reference relationship must be deleted or the current table must be deleted.

2. Specify foreign keys when creating tables

Create two tables, the student table and the score table. The value of the sid column of the score table refers to the student table (the sid column of the student table is set as the primary key, and the storage engine of the table is innodb, the storage engine of the score table 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 into the student table

insert into student values ​​(1,'Sun Wukong')

Insert a record into the grade table, the student number is 1, success.

instert into score values ​​(1,98)

Insert a record into the grade table, the student number is 2, failed.

insert into score values ​​(2,88)

Insert a record with student number 2 into the student table

insert into student values ​​(2,'唐僧')

Then insert a record with student number 2 into the grades table, which is successful, proving that the foreign key reference is successful.

insert into score values ​​(2,88);

3. Delete reference constraints

alter table score drop foreign key score_fk;

4. Add referential constraints to existing tables

alter table score add constraint score_fk2 foreing key (sid) references student (sid);

5. Verify cascading action deletion and update

In the referential integrity created in the score table, cascade is selected for the reference action of the delete action and the update action. When the sid in the student table is updated, the corresponding sid in the score table will also be updated. When the student is deleted, the record of the sid corresponding to the score table will also be automatically deleted.

Update the student table. The student whose student number is 1 is changed to 10.

update student set sid = 10 where sid = 1

Check the score sheet and you can see that the student number 1 has changed to 10.

select * from score

Delete the student whose student number is 2 in the student table

delete from student where sid = 2

You can see the score table, the student's score has been cascade deleted

select * from score

6. Verify cascade action No Aaction

The cascade action is set to NO ACTION. If there are matching records in the child table, update/delete operations on the corresponding candidate key of the parent table are not allowed.

The Restrict action is the same as the no action, both of which check foreign key constraints immediately.

Set the reference action to no action. If the score table has the student sid, the student sid column of the student table cannot be changed, and the student cannot be deleted. Unless you delete the student's grade first and then delete the student.

Delete the foreign key constraint of the score table

alter table score drop foreign key score_fk;

Add a foreign key constraint to the sid column of the score table

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

Update the student ID of student ID 10, failed

update student set sid = 11 where sid = 10

Delete the student with student number 10, failed

delete from student where sid = 10

You need to delete the record in the student score table first, and then delete the student.

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

7. Verify cascade action Set NULL

When updating/deleting records on the parent table, set the columns of the matching records on the child table to null. Note that the foreign key of the child table cannot be not null.

Delete the foreign key constraint of the grades table

alter table score drop foreign key score_fk;

Add a foreign key constraint to the sid column of the grades table, and set the reference action to set null

alter table score add constraint score_fk foreign key (sid) references student (sid) on delete set null on update set null;

Modify the default value of the sid column in 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 the student with student number 1 in the student table

delete from student where sid = 1

Check the score table, the column with student number 1 in the score table is NULL

select * from score

Summarize

The above is the editor's introduction to MySQL from deleting the database to running away_Advanced (I) - Data Integrity. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!