目录

约束介绍

主键 Primary key

not null 非空  和unique 唯一 

foreign key 外键

 check

约束练习

约束介绍

基本介绍

        约束用于确保数据库的数据满足特定的商业规则.在mysql中,约束包括: not null、unique,primary key, foreign key,和check五种

主键 Primary key

1.Data used to define a uniquely labeled table row,The column cannot be repeated when the primary key is constrained.

2.The column indicating the primary key cannot be empty.

3,一个表中只能有一个主键,但可以是复合主键

4.主键1There are two ways to specify Specified directly after the field primary key,Or write at the end of the table definitionprimary key（列名,列名,....）

5.在实际开发中,每个表往往都会设计一个主键.

-- Demonstrates adding a composite primary key(id,和 name 做成复合主键)
CREATE TABLE t01
	(`id` int,
	`name` VARCHAR(10),
	`email` VARCHAR(20),
	PRIMARY KEY (id , `name`))-- 这里就是复合主键

The role of composite primary key is,当往t01 表里添加 id 和name 时, Two values ​​cannot be the same as a piece of data in the table at the same time.

not null 非空  和unique 唯一 

-- unqiue使用细节,similar to primary key,But the primary key cannot be null.

 --如果没有指定not null ,则unique字段可以有多个nu1

--一张表可以有多个unique字段

foreign key 外键

         如上图,jack和班级表的id=200A foreign key join occurred,那么直接删除id = 200The record will fail,只有先把jack然后再删除 id= 200 才行.

-- 	外键
CREATE TABLE my_class(
			id INT PRIMARY KEY ,
			`name` VARCHAR(10) not NULL DEFAULT '');
			
CREATE TABLE my_stu(
			id INT PRIMARY KEY,
			`name` VARCHAR(10) not NULL DEFAULT '',
			class_id INT,
			FOREIGN KEY (class_id) REFERENCES my_class(id)) -- 设置外键

-- 测试数据
INSERT INTO my_class
		VALUES(100,'java'),(200,'web')
INSERT INTO my_stu
		VALUES(1,'tom',100)-- 加入成功
INSERT INTO my_stu
		VALUES(2,'som',200)-- 加入成功		
INSERT INTO my_stu
		VALUES(3,'rose',300)-- 加入失败	
	
	DELETE FROM my_class
	WHERE id = 100 -- 删除失败
		DELETE FROM my_class
	WHERE id = 200 -- 删除失败

 If the foreign key is set, it has no effect because of the problem of the database engine,The engine should be specified when the table is createdInnoDB

 check

CREATE table ee
( `id` INT,
	sex VARCHAR(4) CHECK (sex IN('man','woman')),
	sal int CHECK (sal BETWEEN 1000 AND 2000))
	ENGINE INNODB

INSERT INTO ee
VALUES (1,'man',1500)
INSERT INTO ee
VALUES (1,'xx',1500) -- Logically, this statement does not take effect,But it is used5,7Versions are only checked for syntax,check并不生效
	

约束练习

CREATE TABLE goods (
				goods_id INT PRIMARY KEY auto_increment,
				unitprice INT CHECK (sal BETWEEN 1 and 9999),
				category VARCHAR(10),
				provider VARCHAR(10))
				ENGINE INNODB;

CREATE TABLE customer(
					customer_id INT PRIMARY KEY auto_increment,
					`name` VARCHAR(10) NOT null,
					address VARCHAR(20),
					email VARCHAR(20) UNIQUE,
					sex ENUM('男','女') NOT NULL,
					card_id int)
					CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB
					
CREATE TABLE purchase (
					order_id INT PRIMARY KEY auto_increment,
					customer_id INT,
					goods_id INT,
					nums INT,
		FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
		FOREIGN KEY (goods_id) REFERENCES goods(goods_id))