Data integrity for MySQL database

The data stored in the database should meet our expectations, which is data integrity. So how do you achieve data integrity?

We achieve the integrity of the data in the following two ways:

1. Data type: All data values stored in the database are in the correct state. If an incorrect data value is stored in the database, the database is called lost data integrity.
2. Data constraints: Additional requirements are added on the basis of the data type qualification.

First, the data type

The data type affects the most basic type constraint and space size of data stored in the data.

• Only data of the type that meets the requirements can be stored this is a requirement for data integrity.
• The principle of using data types is: Suffice it, try to use a small range of values, instead of large, so that you can save more storage space

The common data types are as follows:

• Integer: Int,bit
• Decimal: Decimal
• String: Varchar,char
• DateTime: Date, Time, datetime
• Enum type (enum)

The types of special instructions are as follows:

• decimal denotes floating-point numbers, such as decimal (5, 2), which represent 5-bit coexistence and decimals as 2 bits.
• Char represents a fixed-length string, such as char (3), which fills a space when ' ab ' is filled ‘ab ‘ .
• VARCHAR represents a variable-length string, such as varchar (3), which stores ' ab ' when populated with ' AB '
• For pictures, audio, video and other files, not stored in the database, but upload to a server, and then in the table to store the file's save path
• The string text means storing large text, which is recommended when the character is greater than 4000, such as a blog

Second, data constraints

Constraints are inherently additional requirements for data to be added on the basis of data type qualification.

The common constraints are as follows:

• Primary key PRIMARY key: Used to uniquely identify a record. MySQL recommends that all tables have a primary key field called ID, which is of type int unsigned.
• NonEmpty NOT NULL: This field does not allow empty values to be filled in.
• Unique: The value of this field does not allow duplicates.
• Default: The default value is used when the value is not populated, and is filled in as appropriate.
• Foreign key foreign key: the relationship field is constrained, when the value is filled in for the relationship field, the value exists in the associated table, if it exists, the fill succeeds if it does not exist and throws an exception.

Note: Although foreign key constraints can guarantee the validity of data, in the data crud (increase and decrease), will reduce the performance of the database, in this case is not recommended to use

Finally, a table of common data types is attached.

1. Integer type

type	byte size	signed Range (signed)	unsigned range (Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
Mediumint	3	-8388608 ~ 8388607	0 ~ 16777215
Int/integer	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

2. String

type	byte size	Example
CHAR	0-255	Type: char (3) input ' ab ', actually stored as ' ab ', input ' ABCD ' is actually stored as ' abc '
VARCHAR	0-255	Type: varchar (3) Output ' ab ', actually stored as ' ab ', input ' ABCD ', actually stored as ' abc '
TEXT	0-65535	Large text

3. Date and Time type

type	byte size	Example
DATE	4	' 2020-01-01 '
Time	3	' 12:29:59 '
Datetime	8	' 2020-01-01 12:29:59 '
Year	1	' 2017 '
TIMESTAMP	4	' 1970-01-01 00:00:01 ' UTC ~ ' 2038-01-01 00:00:01 ' UTC

Data integrity for MySQL database