MySQL數(shù)據(jù)庫約束詳解

更新時間：2022年10月08日 08:20:50 作者：一刀一個小西瓜

mysql常見約束有：1、非空約束“not?null”；2、唯一性約束“unique”；3、主鍵約束“primary?key”；4、外鍵約束“foreign?key”；5、默認值約束“Default”等等。

基本介紹

約束用于確保數(shù)據(jù)庫的數(shù)據(jù)滿足特定的商業(yè)規(guī)則

在mysql中，約束包括：not null，unique，primary key，foreign key 和check 5種

1.primary key(主鍵)

字段名 字段類型 primary key

于唯一地標識表行的數(shù)據(jù)，當定義主鍵約束之后，該列不能重復

細節(jié)說明

primary key不能重復而且不能為null
一張表最多只能有一個主鍵，但是可以是復合主鍵
主鍵的指定方式有兩種
- 直接在字段名后面指定：字段名 primary key
- 在表定義最后寫 primary key（列名）
使用desc 表名，可以看到primary key的情況
在實際開發(fā)中，每一張表往往都會設(shè)計一個主鍵

例子

-- 主鍵的使用

-- id name email
CREATE TABLE t17(
id INT PRIMARY KEY, -- 表示id列是主鍵
`name` VARCHAR(32),
email VARCHAR(32)
);
INSERT INTO t17 VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17 VALUES(2,'tom','tom@sohu.com');

-- 1. primary key不能重復而且不能為null
INSERT INTO t17 VALUES(NULL,'jack','jack@sohu.com');-- 插入失敗，Column 'id' cannot be null

-- 2. 一張表最多只能有一個主鍵，但是可以是復合主鍵（比如id+name）
-- 演示復合主鍵
CREATE TABLE t18(
id INT, 
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- 這里就是復合主鍵
);

INSERT INTO t18 VALUES(1,'tom','tom@sohu.com'); -- ok
INSERT INTO t18 VALUES(1,'jack','jack@sohu.com');-- OK
INSERT INTO t18 VALUES(1,'tom','12345@sohu.com'); -- false
SELECT * FROM t18;

-- 3. 主鍵的指定方式有兩種
-- 3.1直接在字段名后面指定：字段名 primary key
CREATE TABLE t19(
id INT PRIMARY KEY, 
`name` VARCHAR(32),
email VARCHAR(32)
);

-- 3.2在表定義最后寫 primary key（列名）
CREATE TABLE t20(
id INT, 
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id) 
);

-- 4. 使用desc 表名，可以看到primary key的情況
DESC t20; -- 查看t20的情況，顯示約束的情況

2.not null(非空)

如果在列上定義了not null，那么當插入數(shù)據(jù)時，必須為列提供數(shù)據(jù)

字段名 字段類型 not null

3.unique(唯一)

當定義了唯一約束后，該列值是不能重復的

字段名 字段類型 unique

unique細節(jié)

如果沒有指定not null，則unique字段可以有多個null
一張表可以有多個unique字段

-- unique的使用

CREATE TABLE t21(
id INT UNIQUE, -- 表示id列不可重復 
`name` VARCHAR(32),
email VARCHAR(32)
)
INSERT INTO t21 VALUES(1,'jack','jack@sohu.com'); -- ok
INSERT INTO t21 VALUES(1,'tom','tom@sohu.com'); -- false，Duplicate entry '1' for key 'id'

-- unique細節(jié)

-- 1. 如果沒有指定not null，則unique字段可以有多個null
-- 如果一個列（字段），是 unique not null 則使用效果類似 primary key
INSERT INTO t21 VALUES(NULL,'tom','tom@sohu.com');-- ok
SELECT * FROM t21;

-- 2. 一張表可以有多個unique字段
CREATE TABLE t22(
id INT UNIQUE, -- 表示id列不可重復 
`name` VARCHAR(32) UNIQUE, -- 表示name也不可以重復
email VARCHAR(32)
)
DESC t22;

4.foreign key(外鍵)

foreign key(外鍵)

用于定義主表和從表之間的關(guān)系：

外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束。

當定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null

語法

foreign key(本表字段名) references 主表名(主鍵名或unique字段名)

例子

-- 外鍵演示
-- 創(chuàng)建 主表 my_class
CREATE TABLE my_class(
id INT PRIMARY KEY, -- 班級編號
`name` VARCHAR(32) NOT NULL DEFAULT ''
);

-- 創(chuàng)建從表 my_stu
CREATE TABLE my_stu(
id INT PRIMARY KEY,-- 學生編號
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT, -- 學生所在的班級的編號
-- 下面指定外鍵關(guān)系
FOREIGN KEY(class_id) REFERENCES my_class(id)
);

-- 測試數(shù)據(jù)
INSERT INTO my_class VALUES(100,'java'),(200,'web');
INSERT INTO my_class VALUES(300,'php');
SELECT * FROM my_class;

INSERT INTO my_stu VALUES(1,'tom',100);
INSERT INTO my_stu VALUES(2,'jack',200);
INSERT INTO my_stu VALUES(3,'hsp',300); 
INSERT INTO my_stu VALUES(4,'mary',400); -- 失敗，因為400號班級不存在

SELECT * FROM my_stu;

外鍵細節(jié)說明

外鍵指向的主表的字段，要求是primary key 或是unique
表的類型是innodb，這樣的表才支持外鍵
外鍵字段的類型要和主鍵字段的類型一致（長度可以不同）
外鍵字段的值，必須在主鍵字段中出現(xiàn)過，或者為null [前提是外鍵字段允許為null]
一旦建立主外鍵的關(guān)系，數(shù)據(jù)就不能隨意刪除了

5.check

check

用于強制行數(shù)據(jù)必須滿足的條件。假定在sal列上定義了check約束，并要求sal列值在1000~2000之間，如果不在此范圍，就會提示出錯

提示：oracle和sql server均支持check，但是mysql5.7目前還不支持check，只做語法校驗，但不會生效

語法

列名 類型 check(check條件)

在mysql中實現(xiàn)check的功能，一般是在程序中控制或者通過觸發(fā)器完成

例子

-- 演示check的使用
-- 目前mysql5.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)
);

6.練習

商店售貨系統(tǒng)表設(shè)計案例

現(xiàn)有一個商店的數(shù)據(jù)庫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
- 購買數(shù)量 nums

建表，在定義中要求聲明[進行合理設(shè)計]

每個表的主外鍵
客戶的姓名不能為空值
電郵不能夠重復
客戶的性別[男|女]
單價unitprice在1.0~9999.99之間

-- 使用約束的課堂練習

-- 1,創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE shop_db;

-- 2,商品表 goods
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 BETWEEN 1.0 AND 9999.99), 
category INT NOT NULL DEFAULT 0, -- 商品類別
provider VARCHAR(64) NOT NULL DEFAULT '' -- 供應商
);

-- 3,客戶表 customer
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY, -- 客戶號
`name` VARCHAR(64) NOT NULL DEFAULT '', -- 姓名
address VARCHAR(64) NOT NULL DEFAULT '', -- 地址
email VARCHAR(64) UNIQUE NOT NULL, -- 電郵
sex ENUM('男','女') NOT NULL, -- 使用枚舉類型，性別
card_Id CHAR(18) -- 身份證
);

-- 4,購買表 purchase
CREATE TABLE purchase(
order_id INT PRIMARY KEY, -- 訂單號
customer_id CHAR(8) NOT NULL DEFAULT '', -- 客戶號
goods_id INT NOT NULL DEFAULT 0, -- 商品號
num INT NOT NULL DEFAULT 0, -- 購買數(shù)量 
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
FOREIGN KEY(goods_id) REFERENCES goods(goods_id)
);

DESC goods;
DESC customer;
DESC purchase;

7.自增長

語法

字段名 整型 primary key auto_increment

例子：在某張表中，存在一個id列（整數(shù)）

我們希望在添加記錄的時候，該列從1開始自動地增長，應該怎么處理？

-- 自增長演示
-- 創(chuàng)建表
CREATE TABLE t24(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);

DESC t24;
-- 測試自增長的使用

INSERT INTO t24 VALUES(NULL,'jack@qq.com','jack');
INSERT INTO t24 VALUES(NULL,'tom@qq.com','tom');
INSERT INTO t24(email,`name`) VALUES('hsp@qq.com','hsp');
SELECT * FROM t24;

自增長使用細節(jié)

一般來說自增長是和primary key配合使用的
自增長也可以單獨使用 [但是需要配合一個unique]
自增長修飾的字段為整數(shù)型（雖然小數(shù)也可以但是很少這樣使用）
自增長默認從1開始，也可以通過如下命令來修改
```
alter table 表名 auto_increment = xxx;
```
如果添加數(shù)據(jù)時候，給自增長字段（列）指定有值，則以指定的值為準。一旦指定了值，下一次自增長就會以指定的值開始增長。因此，如果指定了自增長，一般來說就按自增長的規(guī)則來添加數(shù)據(jù)

-- 修改默認的自增長開始值
CREATE TABLE t25(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
ALTER TABLE t25 AUTO_INCREMENT =100;
INSERT INTO t25 VALUES(NULL,'jack@qq.com','jack');
-- 指定值
INSERT INTO t25 VALUES(666,'hsp@qq.com','hsp');
-- 一旦指定了值，下一次自增長就會以指定的值開始增長
INSERT INTO t25 VALUES(NULL,'marry@qq.com','marry');
SELECT * FROM t25;