mysql 创建约束_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)

);