What is the constraint of Mariadb table and its three normal forms 03/26 Update SLTechnology News&Howtos

What is the constraint of Mariadb table and its three normal forms

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

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

Today, the editor will share with you the relevant knowledge points about what Mariadb table constraints and three paradigms are. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

Three paradigms of database

Normal form (NF): when designing a relational database, we should comply with different specification requirements and design a reasonable relational database. These different specifications are called different paradigms, and various paradigms are hierarchical normalization. The higher the paradigm, the less redundant the database. However, sometimes the ignorant pursuit of paradigm to reduce redundancy will reduce the efficiency of data reading and writing, so it is necessary to reverse the paradigm and use space for time. It can be roughly understood as the level of some design standard that the table structure of a data table conforms to.

1NF

That is, the column of the table is atomic and can not be decomposed, that is, the information of the column can not 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, not a collection, array, record, or other non-atomic data item. If an attribute in an entity has more than one value, it must be split into different attributes. It is commonly understood that only one piece of information is stored in a field.

The above does not conform to the first paradigm, because purchase and sales can be subdivided into purchase quantity, purchase unit, sales unit, sales quantity and so on. 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 meet the second normal form (2NF) must first meet the first normal form (1NF). After meeting the 1NF, all columns in the table must depend on the primary key, and no column has nothing to do with the primary key, that is to say, a table describes only one thing.

For example, the order table only describes the information related to the order, so all fields must be related to the order id; the product table only describes the information related to the product, so all fields must be related to the product id; therefore, both order information and product information cannot appear in the same table, as shown in the following figure:

3NF

The second normal form (2NF) must be satisfied first, which requires that each column in the table is only directly related to the primary key, not indirectly, and each column in the table can only rely on the primary key.

For example, customer-related information is needed in the order table. After the customer table is separated, only one user id is needed in the order table, and there can be no other customer information. Because other customer information is directly related to the user id, not directly to the order id.

Various constraints

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

Summary of Mariadb learning (5): database table constraints and three paradigms Mariadb learning summary (5): database table constraints and three paradigms NOT NULL

Non-null constraint, whether the value of the column is allowed to be NULL, here is very important, many fields (except time? The default value is NULL if not specified, so other values are not equal to NULL except NULL=NULL, such as "", 0, and so on.

Change a field to NOT NULL:

There is another problem, for the default value is NULL but does not specify to insert this field:

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

Check ~ because NULL is the default value, but null is not allowed, so, that is to say, the username field does not have a value. Because of SQL_MODE, it will only warn and not directly report an error. When we specify SQL_MODE as' STRICT_ALL_TABLES', the insert will report the following error:

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

Unique represents the unique constraint: the only constraint is that the column or column combination of the specified table cannot be repeated to ensure the uniqueness of the data. Although the unique constraint does not allow duplicate values, it can be multiple null, and the same table can have multiple unique constraints and multiple column combination constraints. When creating a unique constraint, if you do not give the unique constraint name, the default is the same as the column name, and MySQL creates a unique index on the column of the unique constraint by default.

Add a unique constraint:

MariaDB [mydb] > ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE (username); / / uq_username is the constraint name, and UNIQUE (multiple fields) / / when inserting data with the same user name, it will directly report an error MariaDB [mydb] > INSERT INTO user (username,password) VALUES ('test4','test123'); ERROR 1062 (23000): Duplicate entry' test4' for key 'uq_username'// delete this constraint MariaDB [mydb] > ALTER TABLE user DROP KEY uq_username / / add two-field constraints MariaDB [mydb] > ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE (username,password); / / Test add data MariaDB [mydb] > SELECT * FROM user +-+-+ | id | username | password | regtime | Logtime | logip | +-+-+ | 7 | test2 | test3 | 2018-02-24 16:42:48 | 0000-0000: 00:00 | | | 8 | test3 | test3 | 2018-02-24 16:42:48 | 0000-00-0000: 00:00 | 9 | test4 | test5 | 2018-02-24 16:42:48 | 0000-00-0000: 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) / / violates the unique constraint MariaDB [mydb] > INSERT INTO user (username,password) VALUES ('test4','test5') only if the data of the two fields are the same; 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 and non-null constraint, and the primary key constraint column does not allow repetition or null values. If it is a primary key constraint of a combination of multiple columns, none of these columns are allowed to be null, and the combined values are not allowed to be duplicated. A maximum of one primary key is allowed for each table. A primary key constraint can be created at the column level or at the table level. The primary key name of MySQL is always PRIMARY. When creating a primary key constraint, the system will establish a corresponding unique index on the column and column combination by default.

Do the following:

/ / because there is already a primary key in the table, first delete the primary key 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// tells us that only one field in a table is allowed to grow automatically, and this field must be a primary key, so we have to cancel its automatic growth first. 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: 0MariaDB [mydb] > DESC user +-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (11) | NO | PRI | NULL | / / Delete the primary key MariaDB [mydb] > ALTER TABLE user DROP PRIMARY KEY again Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 Warnings / OK, let's add the primary key ~ the following two ways can be oh ~ 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

A foreign key constraint ensures referential integrity between one or two tables, and a foreign key is a reference relationship between two fields of a table or two fields of two tables. That is to say, the foreign key value of the slave table must be found or empty in the master table. When the record of the master table is referenced by the slave table, the record of the master table is not allowed to be deleted. If you want to delete data, you need to delete the data that depends on the record in the slave table first. Then you can delete the data of the master table. Another way is to cascade delete the data of the child table. Note: the reference column of a foreign key constraint can only refer to a primary key or a column with a unique key constraint in the primary table. it is assumed that the referenced primary table column is not a unique record, then the data referenced from the table is uncertain about the location of the record. The same table can have multiple foreign key constraints.

Now, let's create a GROUP table to record the grouping information of users.

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`))

/ / can be empty, but 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:

The following cascading operations should be noted: ON DELETE CASCADE: when deleting rows in the parent (reference) table, if there are child rows in the child table that depend on the deleted parent rows, then delete them together with the child rows, which is not recommended. ON DELETE SET NULL: 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, do not delete it, but set the foreign key column of the child row to NULL

CHECK

CHECK constraint is to check the CHECK constraint when inserting or updating a row of data into the table. CHECK accepts an expression, if the expression is TRUE, it allows insertion, if the expression is FALSE, it refuses to insert, and CHECK is not supported until the MariaDB10.2 version.

Common CHECK constraints are:

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

Example: check whether the user name length is greater than 0

ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK (CHAR_LENGTH (username) > 0); INSERT INTO user (id,username) VALUES (1scoop'); / * SQL error (4025): CONSTRAINT `non_empty_ name`failed for `test`.`user` * /

This thing looks like a chicken, as if the data are generally judged in the business layer, and the database will just store the data.

These are all the contents of the article "what are Mariadb table constraints and three paradigms". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.