Shulou(Shulou.com)06/01 Report--

MySQL Database Advanced (1)-- data Integrity 1, data Integrity introduction 1, data Integrity introduction

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:

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

B, domain integrity: limit type (data type), format (by checking constraints and rules), possible value range (through foreign key constraints, check constraints, default value definitions, non-empty constraints and rules).

C, referential integrity: when deleting and entering records, referential integrity maintains defined relationships between tables. Referential integrity ensures that key values are consistent in all tables and cannot refer to 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. Implementation of data integrity.

MySQL does not support Check constraints, although you can add check constraints to columns, but it does not work.

2. Implementation of entity integrity 1. Brief 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 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 multiple columns to add unique value constraints, always allowing a record to be null.

Entity integrity, achieved by primary keys and uniqueness constraints, ensures that a column of unique identifiers is recorded in the table. The primary key is divided into Primary key and AUTO_INCREMENT PRIMARY KEY.

2. Primary key

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

The primary key constraint is equivalent to the combination of unique constraint and non-null constraint, the primary key constraint column does not allow repetition, nor does it allow null values; for the primary key constraint of multi-column combination, the columns are not allowed to be null, and the combined values are not allowed to repeat. Each table allows at most one primary key, and establishing a primary key constraint can be created at the column level or at the table level.

A. specify the primary key when creating the table

The first way to specify a primary key when creating a table:

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

The second way to specify a 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 a record in a table that specifies a primary key, duplicate ID is not allowed. If you do not specify a value for the primary key, the default is 0.

The storage engine of type MyISAM 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 discharged according to the order of the values of the primary key.

Alter table product ENGINE=InnoDB

B, increase the primary key

Alter table TStudent add primary key (studentid)

C, delete the primary key

Alter table TStudent drop primary key

3. Self-increasing primary key

AUTO_INCREMENT PRIMARY KEY

If you do not specify a primary key value, it automatically adds 1 to the maximum value of the existing primary key as the primary key for the new record, and the primary key value starts at 1 by default. You can add a self-incrementing primary key to a column of an integer data type.

A. specify the self-increment column when creating the table

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

B. specify a self-incrementing column for an existing table

Alter table TStudent modify column studentID int PRIMARY KEY AUTO_INCREMENT

C. Delete self-incrementing columns from the table

Alter table TStudent modify column studentID int not NULL

Delete self-incrementing column, still primary key, but no self-growing function

4. Compound primary key

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

A. specify the compound primary key when creating the 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 compound primary key to the table

Alter table student add PRIMARY KEY (sudentID,id)

C, delete compound primary key

Alter table student drop PRIMARY KEY

5. Unique constraint

UNIQUE KEY, a unique constraint, specifies that the data of a column and a combination of columns cannot be duplicated.

A. specify the uniqueness constraint when creating the table

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 an existing record in the table has duplicate values, it is not allowed to add uniqueness constraints. Through the aggregate function, you can find records with duplicate values, delete them, and then create uniqueness constraints.

C. Create 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. Delete column uniqueness constraint

Alter table score drop index uc_sname

Domain Integrity 1. Default values

When a new record is inserted into the table, if the field is not assigned a value, the database system automatically assigns 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 ())

Add a default value constraint to a column in the table:

Alter table st modify column subject VARCHAR (20) default computer Science and Technology

Delete the default value constraint for a column in the table:

Alter table st modify column subject VARCHAR (20) default NULL

2. Create a non-empty constraint

Non-null constraints are used to ensure that the value of the current column is not null and that non-null constraints can only appear on the columns of the table object.

Null type characteristics: the value of all types can be null, including int, float and other data types empty string is not equal to NULL,0 or NULL.

A. assign a non-empty constraint to the column when you create the table

Create table score (sname VARCHAR (10) not NULL, score int not NULL)

B. Assign a non-null constraint to the specified column

Alter table score modify column score int not NULL

C. Delete non-empty constraint

Alter table score modify column score int

3. Check check

The check keyword, which works only when inserting a new row or changing an existing row, prevents unqualified values from entering the column and has no effect on null values, because inserting null is equivalent to no insertion. A column can have more than one check.

Age int check (age between 10 and 20)

Currently, MySQL does not support check constraints, and Microsoft MSSQL supports Check constraints, but Check constraints can be specified when creating tables, but they do not work.

IV. Referential integrity 1. Brief introduction to referential integrity

MySQL referential integrity is generally achieved through MySQL foreign keys (foreign key).

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

The foreign key declaration consists of three parts:

A, which column or combination of columns is a foreign key

B, specify the tables and columns referenced by the foreign key

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

If the foreign key constraint specifies a reference action, the master table record will be modified, deleted, and the columns referenced from the table will be modified accordingly, or will not be modified, rejected or set to the default value.

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

2. Specify a foreign key when creating a table

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

Insert into student values (1) 'Sun WuKong')

Insert a record in the score sheet, the student number is 1, successful.

Insert into score values (19998)

Insert a record in the score sheet, the student number is 2, failed.

Insert into score values (2pr 88)

Insert a record with a student number of 2 in the student table.

Insert into student values (2) 'Tang Monk'

Then insert a record with a student number of 2 in the score sheet, which proves that the foreign key reference is successful.

Insert into score values (2pr 88)

3. Delete reference constraints

ALTER TABLEscoreDROP FOREIGN KEYscore_fk

4. Add reference constraints to existing tables

ALTER TABLEscoreADD CONSTRAINTscore_fk2FOREIGN KEY (sid) REFERENCESstudent (sid)

5. Verify the cascade for deletion and update

The referential integrity, delete action and update action created in the score table select cascade (cascade operation). When the sid of the student table is updated, the corresponding sid of the score table score will also be updated, and when the student is deleted, the record of the corresponding sid of the score table will also be deleted automatically.

Update the student form whose student number is 1 and the student number is 10.

Update student set sid=10 where sid=1

Looking at the score sheet, you can see that the student number that used to be 1 has become 10.

Select * from score

Delete the student whose student number is 2

Delete from student where sid=2

You can see that the student's score has been cascaded and deleted.

Select * from score

6. Verify that the cascade is No Aaction.

The cascading action is set to NO ACTION, and update/delete operations on candidate keys corresponding to the parent table are not allowed if there are matching records in the child table.

The Restrict action, like no action, checks for foreign key constraints immediately.

Set the reference action to no action, and if the student sid is in the grade table score, you cannot change the student sid column in the student table student table or delete the student. Unless you delete the student's grade first, and then delete the student.

Delete foreign key constraints on score tables

ALTER TABLEscoreDROP FOREIGN KEYscore_fk

Increase the sid column foreign key constraint of 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's student number is 10, failed

Update student set sid=11 where sid=10

Delete the student whose student number is 10, failed

Delete from student where sid=10

You need to delete the record in the student's transcript before deleting the student.

Delete from student where sid=10delete from score where sid=10;7 and verification cascade as Set NULL

When update/delete records on the parent table, set the column of the matching record on the child table to null, and note that the foreign key column of the child table cannot be not null.

Delete the foreign key constraint of the score sheet

ALTER TABLEscoreDROP FOREIGN KEYscore_fk

Add the foreign key constraint of sid column in the score table. The reference action is set null.

ALTER TABLEscoreADD CONSTRAINTscore_fkFOREIGN KEY (sid) REFERENCESstudent (sid) ON DELETE SET NULL ON UPDATE SET NULL

Change the default value of the sid column of the score table to NULL

ALTER TABLE `score` MODIFY COLUMN `sid` INTEGER (11) DEFAULT NULL;insert into student values (1) 'Sun WuKong') insert into student values (2) 'Zhu Bajie') insert into score values (1) insert into score values (1)

Delete the student with student number 1 in the student table

Delete from student where sid=1

Check the transcript, and the student number 1 in the transcript is listed as NULL.

Select * from score

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.