Detailed explanation of referential integrity in SQL (one-to-one, one-to-many, many-to-many)

1. 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. One-to-many

Related key sentences:
constraint cus_ord_fk foreign key (customer_id) REFERENCES customer(id)
Create customer table - order table. A customer can place multiple orders, and each order can only have one customer.

-- Association (1 to N)

create table customer(

	id int PRIMARY KEY auto_increment,
	name varchar (20) not null,
	address varchar (20) not null

);

create table orders(

	order_num varchar(20) PRIMARY KEY,

	price FLOAT not NULL,

	customer_id int, -- foreign key constraint cus_ord_fk foreign key (customer_id) REFERENCES customer(id)

);

insert into customer(name,adress) values("zs","北京");

insert into customer(name,adress) values("ls","上海");

SELECT * from customer;

INSERT INTO orders values("010",30.5,1);

INSERT INTO orders values("011",60.5,2);

INSERT INTO orders values("012",120.5,1);

SELECT * from orders;

notice: constraint: means constraint. foreign key: foreign key. references: Create a foreign key constraint named customer_id_fk, where the foreign key is customer_id and the reference is the id column in the customers table.

Extensions:

Deletion and update strategy of word table:

1. 1) CASCADE cascade strategy. When this strategy is used, the sub-table will be deleted or modified synchronously when the records of the main table are deleted or the primary key field is modified.
2. 2) NO ACTION No action strategy. When using this strategy, you must delete the child table first to delete the main table. You must delete the records associated with the child table first to delete the records in the main table. You cannot update the value of the primary key field in the main table.
3. 3) RSTRICT main table constraint strategy. This strategy has the same constraints on the main table as NO ACTION
4. 4) SET NO clear strategy. When using this strategy, if the primary table is deleted or the primary key is changed, the foreign keys in the child tables are 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 change of the primary key are the same as NO ACTION.
   

2. Many-to-Many

Create a student-teacher relationship table where a student can have multiple teachers and a teacher can have multiple students

-- Many-to-many relationship -- Create a teacher table CREATE TABLE teacher (
	id INT,
	NAME VARCHAR (20) NOT NULL,
	gender CHAR (2) NOT NULL,
	PRIMARY KEY (id)
);

-- Create a student table CREATE TABLE student (
	id INT,
	NAME VARCHAR (20) NOT NULL,
	age INT NOT NULL
);

ALTER TABLE student ADD PRIMARY KEY (id);

-- The third relation table CREATE TABLE tch_std (
	teacher_id INT,
	student_id INT,
	CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (id),
	CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES teacher (id)
);

DELETE
FROM
	teacher
WHERE
	id = 3;

SELECT
	*
FROM
	teacher;

SELECT DISTINCT
	*
FROM
	tch_std;

Attention: Many-to-many is three tables, the third table creates a foreign key constraint corresponding to the id in the first two tables

3. One-to-one

Two forms:

1) Add a unique constraint to the foreign key in the IdCard table according to the foreign key association
2) Add foreign key constraints to the primary key according to the primary key association

-- One-to-one -- Create the users table CREATE TABLE users (
	id INT,
	NAME VARCHAR (20) NOT NULL,
	gender CHAR (2) NOT NULL,
	PRIMARY KEY (id)
);

-- Create the card table CREATE TABLE card (
	id INT,
	address VARCHAR (100) NOT NULL,
	user_id INT UNIQUE,
	constraint users_id_fk foreign key(user_id) references users(id)
);

ALTER TABLE card ADD PRIMARY KEY (id);

This is method 1, adding a unique constraint unique to the foreign key

The above is the detailed explanation and integration of referential integrity in SQL 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!