mysq - constraint/约束/主键/非空/唯一/默认值/check

目录

约束与索引区别

MySQL表级列级约束

MySQL约束类型

MySQL约束的语法

MySQL约束类型举例

1. [NOT] NULL约束

2. CHECK约束

3. UNIQUE约束

4. DEFAULT约束

5. AUTO_INCREMENT约束

6. PRIMARY KEY约束

7. FOREIGN KEY约束

约束与索引区别

索引只是加快查询速度，而约束是限制数据必须符合指定条件

MySQL约束是一系列规则，用来约束（允许/限制）存储在列中的数据。施加约束的目的是为了保证数据库的一致性。
MySQL约束是在创建数据表的时候声明的。

MySQL表级列级约束

  • 列级（column level）约束：作用于某一列。
  • 表级（table level）约束：作用于整个表。

MySQL约束类型

约束名称描述
NOT NULL非空
UNIQUE取值不允许重复
PRIMARY KEY主键（主关键字）
FOREIGN KEY外键（外关键字）
CHECK逻辑表达式约束
DEFAULT默认值（缺省值）

1)非空约束：not null;指示某列不能存储 NULL 值
(2)唯一约束：unique() ,unique约束的字段，要求必须是唯一的，但null除外；简称uk
(3)主键约束：primary key();主键约束=not null + unique，确保某列（或两个列多个列的结合）有唯一标识，简称pk
(4)外键约束：foreign key() references(参考),保证一个表中的数据匹配另一个表中的值的参照完整性,简称fk  
(5)自增约束：auto_increment
(6)默认约束：default 给定默认的值
(7)检查性约束：check  保证列中的值符合指定的条件

顺便介绍PRI,UNI,MUL
-- 如果键是PRI，则列是主键或多列主键中的列之一。
-- 如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)
-- 如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值。

 

MySQL约束的语法

CREATE TABLE [table name](
[column name] data type [column constraint],
…
[table constraint] ([[column name]…])...
);

//constraint uk_name 可以省略不写。
//uk_name 唯一约束名；
//pk_name 主键约束名
create table member(
id int(10),
phone int(15) unsigned zerofill,
-- 非空约束 
name varchar(30) not null,
-- 唯一约束 
constraint uk_name unique(name),
-- 主键约束
constraint pk_name primary key (col),
-- 外键约束
constraint fk_name foreign key (col1) references table_name(col2),

-- 建表带主键 
CREATE TABLE score1(score DOUBLE,sid INT,CONSTRAINT fk_stu_score1_sid primary key(sid));
-- 为已存在的表建主键
ALTER TABLE score1 ADD CONSTRAINT  pk_name primary key(sid);
-- 建表添加外键
CREATE TABLE score1(score DOUBLE,sid INT,CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid));
-- 为已存在的表建外键

 

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

版权声明：本文为helunqu2017原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。