详解sql中的参照完整性（一对一，一对多，多对多）

-- 关联（1对N）
create table customer(
	id int PRIMARY KEY auto_increment,
	name varchar (20) not null,
	adress varchar (20) not null
);
create table orders(
	order_num varchar(20) PRIMARY KEY,
	price FLOAT not NULL,
	customer_id int, -- 进行和customer 关联的字段 外键
	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;

-- 多对多关系
-- 创建teacher表格
CREATE TABLE teacher (
	id INT,
	NAME VARCHAR (20) NOT NULL,
	gender CHAR (2) NOT NULL,
	PRIMARY KEY (id)
);
-- 创建学生表格
CREATE TABLE student (
	id INT,
	NAME VARCHAR (20) NOT NULL,
	age INT NOT NULL
);
ALTER TABLE student ADD PRIMARY KEY (id);
-- 第三张关系表
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;

-- 一对一
-- 创建users表格
CREATE TABLE users (
	id INT,
	NAME VARCHAR (20) NOT NULL,
	gender CHAR (2) NOT NULL,
	PRIMARY KEY (id)
);
-- 创建card 表格
CREATE TABLE card (
	id INT,
	adress 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);