MySQL约束是一系列规则，用来约束（允许/限制）存储在列中的数据。施加约束的目的是为了保证数据库的一致性。
MySQL约束是在创建数据表的时候声明的。
MySQL可以实现的不同级别的约束
- 列级（column level）约束：作用于某一列。
- 表级（table level）约束：作用于整个表。
MySQL约束类型
| 约束名称 | 描述 |
|---|---|
| NOT NULL | 非空 |
| UNIQUE | 取值不允许重复 |
| PRIMARY KEY | 主键（主关键字） |
| FOREIGN KEY | 外键（外关键字） |
| CHECK | 逻辑表达式约束 |
| DEFAULT | 默认值（缺省值） |
MySQL约束的语法
CREATE TABLE [table name](
[column name] data type [column constraint],
…
[table constraint] ([[column name]…])...
);
MySQL约束类型举例
1. [NOT] NULL约束
CREATE TABLE IF NOT EXISTS newauthor(
aut_id varchar(8) NOT NULL,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL
);
2. CHECK约束
-
值约束
CREATE TABLE IF NOT EXISTS newbook_mast (
book_id varchar(15) NOT NULL UNIQUE,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL UNIQUE ,
cate_id varchar(8) ,
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0)
CHECK(no_page>0) , # check constraint
book_price decimal(8,2) ,
PRIMARY KEY (book_id)
);
-
取值集合约束
CREATE TABLE IF NOT EXISTS newauthor(
aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')),
home_city varchar(25) NOT NULL,
PRIMARY KEY (aut_id,home_city)
);
-
模糊取值约束
CREATE TABLE IF NOT EXISTS newbook_mast(
book_id varchar(15) NOT NULL UNIQUE,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL UNIQUE ,
cate_id varchar(8) ,
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date CHECK (dt_of_pub LIKE '--/--/----'),
pub_lang varchar(15) ,
no_page decimal(5,0) CHECK(no_page>0) ,
book_price decimal(8,2) ,
PRIMARY KEY (book_id)
);
-
带逻辑操作的约束
CREATE TABLE IF NOT EXISTS newpublisher(
pub_id varchar(8) ,
pub_name varchar(50),
pub_city varchar(25) ,
country varchar(25) ,
country_office varchar(25) ,
no_of_branch int(3),
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pub_id)
);
3. UNIQUE约束
-
实现方法1（表的定义最后施加）
CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,
UNIQUE (aut_id)
);
-
实现方法2（字段定义的最后施加）
CREATE TABLE IF NOT EXISTS newauthor(
aut_id varchar(8) NOT NULL UNIQUE ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL
);
4. DEFAULT约束
CREATE TABLE IF NOT EXISTS newpublisher(
pub_id varchar(8) NOT NULL UNIQUE DEFAULT '' ,
pub_name varchar(50) NOT NULL DEFAULT '' ,
pub_city varchar(25) NOT NULL DEFAULT '' ,
country varchar(25) NOT NULL DEFAULT 'India',
country_office varchar(25) ,
no_of_branch int(3),
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pub_id)
);
5. AUTO_INCREMENT约束
CREATE TABLE IF NOT EXISTS newauthor(
id int NOT NULL AUTO_INCREMENT,
aut_id varchar(8),
aut_name varchar(50),
country varchar(25),
home_city varchar(25) NOT NULL,
PRIMARY KEY (id)
);
6. PRIMARY KEY约束
-
单个字段作为主键（方法1）
CREATE TABLE IF NOT EXISTS newauthor(
aut_id varchar(8) NOT NULL PRIMARY KEY,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL
);
-
单个字段作为主键（方法2）
CREATE TABLE IF NOT EXISTS newauthor(
aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,
PRIMARY KEY (aut_id)
);
-
多个字段作为主键
CREATE TABLE IF NOT EXISTS newauthor(
aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,
PRIMARY KEY (aut_id, home_city)
);
7. FOREIGN KEY约束
语法：
FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);
-
为单个字段创建外键
CREATE TABLE IF NOT EXISTS newbook_mast (
book_id varchar(15) NOT NULL PRIMARY KEY,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL ,
cate_id varchar(8) ,
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0) ,
book_price decimal(8,2) ,
FOREIGN KEY (aut_id) REFERENCES newauthor(aut_id)
);
-
为多个字段创建外键
CREATE TABLE IF NOT EXISTS newpurchase(
invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id),
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);
-
创建来自不同表的外键
CREATE TABLE IF NOT EXISTS newbook_mast (
book_id varchar(15) NOT NULL PRIMARY KEY,
book_name varchar(50) ,
isbn_no varchar(15) NOT NULL ,
cate_id varchar(8),
aut_id varchar(8) ,
pub_id varchar(8) ,
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0) ,
book_price decimal(8,2) ,
INDEX (aut_id),
FOREIGN KEY(aut_id) REFERENCES newauthor(aut_id),
INDEX(pub_id),
FOREIGN KEY(pub_id) REFERENCES newpublisher(pub_id)
);
-
外键动作的设置
1. UPDATE CASCADE（级联更新）和DELETE RESTRICT（限制删除）
CREATE TABLE IF NOT EXISTS newpurchase(
invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);
2. UPDATE CASCADE（级联更新）和DELETE SET NULL（删除置空）
CREATE TABLE IF NOT EXISTS newpurchase (
invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES neworder
(ord_no,book_id)
ON UPDATE CASCADE ON DELETE SET NULL,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);
3. UPDATE CASCADE（级联更新）和DELETE NO ACTION（删除不操作）
CREATE TABLE IF NOT EXISTS newpurchase (
invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE NO ACTION,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id)
);
参考： https://www.w3resource.com/mysql/creating-table-advance/constraint.php
网友评论