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

This article mainly introduces the example analysis of MySQL data integrity, the article is very detailed, has a certain reference value, interested friends must read it!

Data integrity is divided into: entity integrity, domain integrity, reference integrity.

Referential integrity:

Referential integrity refers to the design between multiple tables, mainly using foreign key constraints.

Multi-watch design: one-to-many, many-to-many, one-to-one design

One: entity (row) integrity

Entity integrity is achieved through the primary key of the table.

Use the primary key to represent the uniqueness of a record and is not empty

Syntax: primary key

Primary key classification:

Logical primary key: for example, ID, which does not represent the actual business meaning, but is used to uniquely identify a record (recommended)

Business primary key: for example, username, which participates in the actual business logic.

Features: unique, not null

Automatic growth: auto_increment

For example:

Create table person2 (id int primary key auto_increment, name varchar)

Two: domain (column) integrity

Non-empty constraints: not null

Unique constraint: unique

Creat table person3 (id int not null unique, name varchar (20) not null unique, gender varchar (20) not null, phone char (11) unique # cannot have,)

III: referential integrity

1, one to many

A: for example, the relationship between customer and order is one-to-many, one customer can have multiple orders, and one order belongs to one customer.

B: the foreign key is set to more than one party

Syntax input creates one-to-many:

Create a customer table:

Create table customers (id int, name varchar (20), address varchar (100), primary key (id))

Create an order table:

Create table orders (id int, order_num int primary key, price float (4jue 2), customer_id int, constraint customer_ord_fk foreign key (customer_id) references customers (id))

Note: constraint: constraint, foreign key: foreign key, references: referenc

Create a foreign key constraint called customer_ord_fk, which refers to customer_id and refers to the id column in the customers table

Table operations create one-to-many

Customers table

Orders table

Orders Foreign key Settings

Create a foreign key constraint called customer_ord_fk, the foreign key field customer_id, referring to the id column in the customers table

Extension: delete and update strategies for four child tables:

1 cascade cascade strategy: when using this strategy, the records of the primary table are deleted or the primary key fields are modified, the child tables are deleted or modified synchronously.

2 no action no action strategy: when using this strategy, to delete the primary table, you must first delete the child table. To delete the records of the primary table, you must delete the records associated with the child table. You cannot update the value of the primary key field of the primary table.

3The master table constraint strategy. This strategy has the same constraints on the primary table as NO ACTION does.

4Dem set no null policy. When using this strategy, if the primary table is deleted or the primary key is changed, the foreign key in the child table is set to null. It should be noted that if the foreign key of the child table is the primary key or is set to not null, the deletion of the primary table and the change of the primary key are the same as no action

2, many to many

Create a teacher table:

Create table teachers (id int primary key auto_increment, name varchar (20), gender char (5), subject varchar (20))

Create a student table:

Create table students (id int primary key, name varchar (20), age int)

The third is the associated table:

Create table teacher_student (id int, t_id int, s_id int, primary key) constraint teacher_id_fk foreign key (t_id) references students (id), constrains student_id_fk foreign key (s_id) references teachers (id))

Many-to-many table operations

Create a teacher table:

Create a student table:

The third table:

3, one on one

(1) in general, you can design a form.

(2) designed into two forms

Method 1: create a field in the second table as a foreign key (set to a unique unique) corresponding to the primary key in the first table

Method 2: directly use the primary key in the second table as the foreign key, corresponding to the primary key of the first table.

Two tables: unique is very important

Create table user (id int primary key auto_increment,name varchar (20), age int); create table card (id int primary key auto_increment,num varchar (20) not null,address varchar (100) not null,user_id int unique,constraint user_card_fk foreign key (user_id) references user (id)); the above is all the content of this article "sample Analysis of MySQL data Integrity". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.