MySQL常见的建表约束（Create Table Constraints）

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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,772评论 6赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,458评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,610评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,640评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,657评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,590评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,962评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,631评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,870评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,611评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,704评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,386评论 4赞 319
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,969评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,944评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,179评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,742评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,440评论 2赞 342