Mariadb learning summary (5): database table constraints and three paradigms

Three major database paradigms

Normal Form (NF): When designing a relational database, follow different normative requirements to design a reasonable relational database. These different normative requirements are called different paradigms. Various paradigms are presented in different specifications. The higher the paradigm database redundancy The more I get, the smaller I get. However, sometimes blindly pursuing paradigms to reduce redundancy will actually reduce the efficiency of data reading and writing. At this time, it is necessary to reverse the paradigm and use space to trade time. It can be roughly understood as the level of a certain design standard that the table structure of a data table conforms to.

1NF That is, the columns of the table are atomic and cannot be decomposed, that is, the column information cannot be decomposed. As long as the database is a relational database (mysql/oracle/db2/informix/sysbase/sql server), it automatically satisfies 1NF. Each column of a database table is an indivisible atomic data item, and cannot be a collection, array, record and other non-atomic data items. If an attribute in an entity has multiple values, it must be split into different attributes. The popular understanding is that a field only stores one piece of information.

The above does not conform to the first paradigm, because purchase and sales can be further divided into purchase quantity, purchase unit, sales unit, sales quantity, etc. The following meets the first paradigm.

2NF The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to satisfy the second normal form (2NF), the first normal form (1NF) must first be satisfied. After satisfying 1NF, it is required that all columns in the table must depend on the primary key, and there cannot be any column that has no relationship with the primary key, which means that a table only describes one thing;

For example: the order table only describes order-related information, so all fields must be related to the order id; the product table only describes product-related information, so all fields must be related to the product id; therefore, order information cannot appear in one table at the same time and product information; as shown below:

3NF It must first satisfy the second normal form (2NF), which requires: Each column in the table is only directly related to the primary key and not indirectly related. Each column in the table can only depend on the primary key.

For example: the order table needs to have customer-related information. After the customer table is separated, the order table only needs to have a user ID and no other customer information. Because other customer information is directly related to the user ID, not directly related to the order ID.

Various constraints

Constraints are a set of rules used to limit the accuracy, completeness, consistency, and linkage of data in a table. In Mysql, constraints are stored in table_constraints of the information_schema database, and constraint information can be queried through this table. As shown below:

NOT NULL

Non-null constraint, whether the value of this column is allowed to be NULL, one thing is very important here, the default value of many fields (except time?) is NULL if not specified, so except NULL=NULL, other values ​​are not equal to NULL. Such as "", 0, etc.

Modify a field to NOT NULL:

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

MariaDB [mydb]> ALTER TABLE user MODIFY logip varchar(20) NOT NULL;
Query OK, 5 rows affected, 5 warnings (0.04 sec)   
Records: 5  Duplicates: 0  Warnings: 5

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | NO   |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)

There is another problem here, for the default value is NULL but the field is not specified to be inserted:

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
//看username这个字段，默认值为NULL，不允许NULL
MariaDB [mydb]> INSERT INTO user(password) VALUES('test7');
Query OK, 1 row affected, 1 warning (0.00 sec)
//这里看到我们插入成功了。
MariaDB [mydb]> SELECT * FROM user WHERE password='test7';
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 12 |          | test7    | 2018-02-25 15:25:14 | 0000-00-00 00:00:00 | NULL  |
+----+----------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

You can see that the value of the username column is a null character, and its default value is NULL,
The default value of logip is NULL, but NULL values ​​are allowed to be inserted, so NULL values ​​are displayed here.

Check it~ Because NULL is the default value, but NULL values ​​are not allowed, so that means that the username field now has no value. Because of SQL_MODE, it will only give a warning and not directly report an error. When we specify SQL_MODE as 'STRICT_ALL_TABLES', the following error will be reported when inserting:

MariaDB [mydb]> INSERT INTO user(password) VALUES('test88');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

UNIQUE

unique represents a unique constraint: a unique constraint means that the columns or column combinations of the specified table cannot be repeated to ensure the uniqueness of the data. Although the unique constraint does not allow duplicate values, can be multiple null, and the same table can have multiple A unique constraint, a constraint combining multiple columns. When creating a unique constraint, if you do not give a unique constraint name, it will default to the same as the column name, and MySQL will create a unique index by default on the column of the unique constraint.

Add unique constraint:

MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username);  //uq_username为约束名称，UNIQUE(可多个字段）

//当插入用户名相同的数据事则会直接报错

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
ERROR 1062 (23000): Duplicate entry 'test4' for key 'uq_username'

//删除此约束
MariaDB [mydb]> ALTER TABLE user DROP KEY uq_username;

//添加两个字段的约束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);

//测试添加数据
MariaDB [mydb]> SELECT * FROM user;                                                     
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
|  7 | test2    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  8 | test3    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  9 | test4    | test5    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
+----+----------+----------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
Query OK, 1 row affected (0.01 sec)

//仅当两个字段的数据都相同时才违反唯一约束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'

PRIMARY KEY

The primary key constraint is equivalent to the combination of unique constraint + non-null constraint. The primary key constraint column does not allow duplication or null values. If it is a primary key constraint that combines multiple columns, none of these columns are allowed to have null values, and the combined values ​​are not allowed to be repeated. Each table only allows one primary key at most. The primary key constraint can be created at the column level or the table level. The primary key name of MySQL is always PRIMARY. When creating a primary key constraint, the system will default to the column and table where it is located. Create a corresponding unique index on the column combination.

Operation is as follows:

//因为现在的表中已经有主键了，先把主键删掉
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

//告诉我们一张表里只允许有一个字段为自动增长，且这个字段必须是主键，所以，我们要先取消它的自动增长。

MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id int(11) NOT NULL;
Query OK, 4 rows affected (0.07 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+-------+
| Field    | Type        | Null | Key | Default             | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id       | int(11)     | NO   | PRI | NULL                |       |


//再次删除主键
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 4 rows affected (0.03 sec)               
Records: 4  Duplicates: 0  Warnings: 0

//好了，再让我们把主键加上吧~~~   以下两种方式都可以哦~
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT PRIMARY KEY(id);
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;

FOREIGN KEY

Foreign key constraints ensure referential integrity between one or two tables . Foreign keys are built on the reference relationship between two fields of one table or two fields of two tables . That is to say, the foreign key value of the slave table must be found in the master table or be empty. When the records of the master table are referenced by the slave table, the records of the master table will not be deleted. If you want to delete the data, you need to delete the slave table first. The data in the table depends on the record, and then the data of the main table can be deleted. Another way is to cascade delete the data of the sub-table. Note: The reference column of a foreign key constraint can only refer to the column of the primary key or unique key constraint in the main table. Assuming that the referenced main table column is not the only record, then the data referenced from the table will not be sure of the record location. The same table can have multiple foreign key constraints.
Now, let’s create a GROUP table to record the user’s group information,

 CREATE TABLE `usergroup` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `comment` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 |

Then~ add a record to the user table to record which group the user belongs to

MariaDB [mydb]> ALTER TABLE user ADD COLUMN groupid INT(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

//Add a foreign key

ALTER TABLE user ADD CONSTRAINT fk_groupid FOREIGN KEY (groupid) REFERENCES usergroup(id);

//Verify foreign key constraints

MariaDB [mydb]> INSERT INTO user(username,password,groupid) VALUES('test99','test00',1); 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user`, CONSTRAINT `fk_groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`id`))

//It can be empty, but it cannot be a value that is not in the reference table

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test99','test00');
Query OK, 1 row affected (0.01 sec)

Foreign key definition:

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

The following cascading operations require attention:

ON DELETE CASCADE: When deleting a row in the parent (reference) table, if there are child rows in the child table that depend on the deleted parent row, then the child rows will be deleted together. This is not recommended.

ON DELETE SET NULL: When deleting a row in the parent (reference) table, if there is a child row in the child table that depends on the deleted parent row, then it will not be deleted, but the foreign key column of the child row will be set to NULL

CHECK CHECK constraint is to perform CHECK constraint check when inserting a row or updating a row of data into the table. CHECK accepts an expression. If the expression is TRUE, the insertion is allowed. If the expression is FALSE, the insertion is rejected. In MariaDB10.2 Version only started to support CHECK.

Common CHECK constraints include:

CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0)
CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date 
<p>Example: Check if username length is greater than 0</p>
<pre class="brush:php;toolbar:false">
ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK(CHAR_LENGTH(username)>0);
INSERT INTO user(id,username) VALUES(1,'');
/* SQL错误（4025）：CONSTRAINT `non_empty_name` failed for `test`.`user` */

This thing looks very useless. It seems that data judgment is usually done at the business layer, and the database only needs to store the data.

The above is the detailed content of Mariadb learning summary (5): database table constraints and three paradigms. For more information, please follow other related articles on the PHP Chinese website!