Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand the constraint mechanism of relational database. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

one。 Relational database

The mainstream relational databases in the market: Oracle, DB2, Sybase, PostgreSQL, Microsoft SQL Server, Microsoft Access, MySQL.

There are three major integrity of relational databases:

Entity integrity: the identity attribute in the entity attribute cannot be empty or repeatable. The constraint is implemented through the established primary key, and the constraint is enforced by the system.

Referential integrity: foreign keys in entities can be empty, but cannot be wrong.

User-defined integrity: the constraints of a specific actual database, determined by the application environment, reflecting the requirements that the data involved in a specific application must meet. (for example, define that a line cannot be empty)

Constraints mainly complete the inspection and restriction of the data, so as to ensure the integrity of the database.

two。 Constraint

1. Primary key constraint (PRIMARY KEY)

The primary key constraint column does not allow repetition or null values.

Single column primary key

There are two ways to create:

One is to add the primary key keyword directly to the field:

CREATE TABLE student (id INT PRIMARY KEY, name VARCHAR (20),)

The other is through additional constraints:

CREATE TABLE student (id INT NOT NULL, name VARCHAR (20), CONSTRAINT PK_STUD_ID PRIMARY KEY (id) / / PK_STUD_ID is the name of the constraint)

Joint primary key

Sometimes there may be duplicates in the fields of a column, and multiple columns can be combined as primary keys.

CREATE TABLE student (name VARCHAR (20), class VARCHAR (20), CONSTRAINT PK_STUD_ID PRIMARY KEY (name,class)

Other operations

/ / delete primary key constraint ALTER TABLE table name DROP PRIMARY KEY; / / add primary key ALTER TABLE table name ADD PRIMARY KEY (column name); / / modify column primary key ALTER TABLE table name MODIFY column name data type PRIMARY KEY

two。 Foreign key constraint (FOREIGN KEY)

Foreign key constraints ensure referential integrity between one or two tables and maintain data consistency.

Achieve an one-to-one or one-to-many relationship.

Create a foreign key constraint

CREATE TABLE classes (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (20) / / * species: add keywords CREATE TABLE student (id INT AUTO_INCREMENT, name VARCHAR (20), CONSTRAINT PK_ID PRIMARY KEY (id), class_id INT REFERENCES classes (id)) / / second: additional declaration CREATE TABLE student (id INT AUTO_INCREMENT) Name VARCHAR (20), CONSTRAINT PK_ID PRIMARY KEY (id), class_id INT, CONSTRAINT fk_class_id FOREIGN KEY (classe_id) REFERENCES classes (id))

Requirements for foreign key constraints

Parent and child tables must use the same storage engine, and temporary tables are prohibited

The storage engine for data tables can only be InnoDB,default-storage-engine-INNODB.

Foreign key and reference columns must have similar data types, the length of numbers or signed bits must be the same, and the length of characters can be different

Indexes must be created for foreign key columns and reference columns, and MySQL will automatically create indexes if no indexes exist for foreign key columns.

Set reference column: FOREIGN KEY (id) PEFERENCES provinces (id)

Reference operations for foreign key constraints

CASCADE: deletes or updates from the parent table and automatically deletes or updates matching rows in the child table

SET NULL: delete or update rows from the parent table and set the foreign key column NULL in the child table. If you use this option, you must ensure that the child table column does not specify NOT NULL

RESTRICT: rejects delete or update operations on the parent table

NO ACTION: keyword for standard SQL, same as RESTRICT in MySQL

Other operations

/ / delete foreign key constraint ALTER TABLE table name DROP FOREIGN KEY foreign key constraint name; (no constraint name is specified in the method of creating foreign key, the system will assign foreign key constraint name to foreign key constraint by default, named student_ibfk_n) / / add foreign key constraint ALTER TABLE table name ADD FOREIGN KEY column name REFERENCES parent table (corresponding column name)

3. Constraint * constraint (UNIQUE)

Specifies that one or more columns in a table cannot have the same two or more rows of data.

* constraints can guarantee the * * nature of the record.

* the constrained field can be null (multiple null values are allowed, but only one is stored)

There can be multiple * constraints per data table.

The purpose of UNIQUE KEY: it is mainly used to prevent repetition when data is inserted.

CREATE TABLE student (name CHAR (20) UNIQUE KEY)

Add UNIQUE KEY to the columns in the table

ALTER TABLE student MODIFY CHAR (20) UNIQUE KEY

4. Non-null constraint (NOT NULL)

The input value must be non-empty, and it is important to note that you are allowed to control as little as possible, and too much will consume database performance.

CREATE TABLE student (name CHAR (20) NOT NULL)

5. Default constraint (DEFAULT)

Default constraints have the following requirements:

The defined constant must match the data type, precision, etc., of the column.

Only one DEFAULT constraint can be defined per column

The DEFAULT constraint uses the insert statement

It is important to note that DEFAULT cannot be followed by a function like SQL Server, and can only be followed by a constant. Original text of the official document:

With one exception, the default value must be a constant; it cannot be a function or an expression.

If we want to get the current time, we can use CURRENT_TIMESTAMP

CREATE TABLE student (joinTime DATETIME DEFAULT CURRENT_TIMESTAMP)

6.CHECK constraint

CHECK constraints to validate data, such as gender, can only be female or male, not others. MySQL does not support check constraints, but you can use check constraints, but it has no effect.

On how to understand the constraint mechanism of relational database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.