本次博客带领大家学习mysql数据库中的约束。
- 基本介绍:约束用于确保数据库的数据满足特定的商业规则。在mysql中,约束包括:not null、unique、primary key、foreign key和check 五种。
主键约束(primary key)
- primary key(主键)—基本使用
字段名 字段类型 primary key
用于唯一的标识表行的数据,当定义主键约束后,该列不能重复。
- primary key 不能重复而且不能为 null。
INSERT INTO t17 VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17 VALUES(2,'tom','tom@sohu.com');
INSERT INTO t17 VALUES(1,'ld','ld@sohu.com');
INSERT INTO t17 VALUES(NULL,'ld','ld@sohu.com');
- 一张表最多只能有一个主键,但可以是复合主键。
CREATE TABLE t18(
id INT ,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id,`name`)
)
- 主键的指定方式有两种:
- 直接在字段名后指定 :字段名 primakry key。
CREATE TABLE t17(
id INT PRIMARY KEY,-- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32))
- 在表定义最后写primary key(列名)。
CREATE TABLE t19(
id INT ,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(`name`)
)
- 使用desc 表名,可以看到 primakry key 的情况。
DESC t19;
非空约束(not null)
- 如果在列上定义了not null,那么当插入数据时,必须为列提供数据。
字段名 字段类型 not null
唯一约束(unique)
- 当定义了唯一约束后,该列值是不能重复的。
字段名 字段类型 unique
- 如果没有指定 not null,则unique 字段可以有多个null,如果一个列(字段),是unique not null 使用效果类似 primary key。
INSERT INTO t20 VALUES(NULL,'aaa','aaa@sohu.com');
- 一张表可以有多个unique字段。
CREATE TABLE t21(
id INT UNIQUE,
`name` VARCHAR(32) UNIQUE,
email VARCHAR(32)
)
外键约束(foreign key)
- 用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或者是unique约束。当定义外键约束后,要求外键列数据必须在主表的主键列存在或者是为null。
基本语法:foreign key (本表的字段名) references 主表名(主键名或unique字段名)
- 外键指向的表的字段,要求是primary key或者是unique。
- 表的类型是innodb,这样的表才支持外键。
- 外键字段的类型要和主键字段的类型一致(长度可以不同)。
- 外键字段的值,必须在主键字段中出现过,或者为null。[前提是外键字段允许为null]
- 一旦建立主外键的关系,数据不能随意删除了。
检查约束(check)
- 用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在10002000之间,如果不在10002000之间就会出错。
- oracle 和 sql server 均支持check,但是mysql 5.7 目前还不支持check ,只做语法校验,但不会生效。
CREATE TABLE t23(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal >1000 AND sal <2000));
INSERT INTO t23 VALUES(1,'jack','mid',1);
商品销售系统表设计案例
现有一个商店的数据库shop_db,记录客户及其购物情况,由下面三个表组成:
- 商品goods (商品号 goods_id,商品名 goods_name,单价 unitprice,商品类型 category,供应商provider);
- 客户customer (客户号 customer_id,姓名 name,住址 address,电邮 email,性别 sex,身份证 card_id);
- 购买purchase (购买订单号 order_id,客户号 customer_id,商品号 goods_id,购买量 nums);
建表,在定义中要求声明:
- 每个表的主外键;
- 客户的姓名不能为空值;
- 电邮不能够重复;
- 客户的性别[男|女];
- 单价unitprice 在1.0 - 9999.99 之间 check。
CREATE DATABASE shop_db;
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice >=1.0 AND unitprice <=9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT '');
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) NOT NULL UNIQUE,
sex ENUM('男','女') NOT NULL,
card_id CHAR(18) UNIQUE);
CREATE TABLE purchase(
order_id INT PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
DESC goods;
DESC customer;
DESC purchase;