MySQL: Data Integrity

Data integrity is divided into: entity integrity, domain integrity, and referential integrity.

Referential Integrity:

Referential integrity refers to the design between multiple tables, mainly using foreign key constraints.
Multi-table design: one-to-many, many-to-many, one-to-one design

1. Entity (row) integrity

Entity integrity is achieved through the primary key of the table.
Use the primary key to indicate that a record is unique and not empty. Syntax: primary key
Primary key classification:

Logical primary key: such as ID, does not represent actual business significance, but is only used to uniquely identify a record (recommended)
Business primary key: such as username, involved in actual business logic.
Features: unique, not null
Automatic growth: auto_increment

For example:

create table person2(
			id int primary key auto_increment,
			name varchar(100)
);

2. Domain (column) integrity

Not null constraint: 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 #Not allowed,
	);

Three: Referential Integrity

1. One-to-many
a: For example, the relationship between customers and orders is one-to-many. A customer can have multiple orders, and one order belongs to one customer.
b: The foreign key is set to the more party

Syntax input to create one-to-many:
Create the customer table:

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

Create the Orders table:

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

Note: constraint: constraint, foreign key: foreign key, references: references Create a foreign key constraint named customer_ord_fk, the foreign key refers to customer_id, which refers to the id column in the customers table

Table operation creates one-to-many

customers table

orders table

orders foreign key settings

Create a foreign key constraint named customer_ord_fk with the foreign key field customer_id, which refers to the id column in the customers table.

Extension: Four sub-table deletion and update strategies:

1. 1. Cascade strategy: When this strategy is used, when the records of the main table are deleted or the primary key field is modified, the sub-table will be deleted or modified synchronously.
2. 2, no action strategy: When using this strategy, you must delete the sub-table before deleting the main table. You must delete the records associated with the sub-table before deleting the records in the main table. You cannot update the value of the primary key field in the main table.
3. 3, restrict main table constraint strategy. This strategy has the same constraints on the main table as NO ACTION
4. 4, set no to clear the 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 main table and the modification of the primary key are the same as no action.

2. Many-to-many

Create the teacher table:

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

Create the student table:

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

The third one is the association table:

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

Table Operation Many-to-Many

Create the teacher table:

Create the student table:

The third table:

  3. One-to-one

(1) Generally, you only need to design one table. (2) You can design two tables.

Method 1: Create a field in the second table as a foreign key (set to 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 the detailed explanation and integration of MySQL data integrity introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!