MySQL資料庫約束詳解

2022-10-10 14:01:51

基本介紹

約束用於確保資料庫的資料滿足特定的商業規則

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

1.primary key(主鍵)

欄位名 欄位型別 primary key

於唯一地標識錶行的資料，當定義主鍵約束之後，該列不能重複

細節說明

primary key不能重複而且不能為null
一張表最多隻能有一個主鍵，但是可以是複合主鍵
主鍵的指定方式有兩種
- 直接在欄位名後面指定：欄位名 primary key
- 在表定義最後寫 primary key（列名）
使用desc 表名，可以看到primary key的情況
在實際開發中，每一張表往往都會設計一個主鍵

例子

-- 主鍵的使用

-- 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，那麼當插入資料時，必須為列提供資料

欄位名 欄位型別 not null

3.unique(唯一)

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

欄位名 欄位型別 unique

unique細節

如果沒有指定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細節

-- 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(外來鍵)

用於定義主表和從表之間的關係：

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

當定義外來鍵約束後，要求外來鍵列資料必須在主表的主鍵列存在或是為null

語法

foreign key(本表欄位名) references 主表名(主鍵名或unique欄位名)

例子

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

-- 建立從表 my_stu
CREATE TABLE my_stu(
id INT PRIMARY KEY,-- 學生編號
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT, -- 學生所在的班級的編號
-- 下面指定外來鍵關係
FOREIGN KEY(class_id) REFERENCES my_class(id)
);

-- 測試資料
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;

外來鍵細節說明

外來鍵指向的主表的欄位，要求是primary key 或是unique
表的型別是innodb，這樣的表才支援外來鍵
外來鍵欄位的型別要和主鍵欄位的型別一致（長度可以不同）
外來鍵欄位的值，必須在主鍵欄位中出現過，或者為null [前提是外來鍵欄位允許為null]
一旦建立主外來鍵的關係，資料就不能隨意刪除了

5.check

check

用於強制行資料必須滿足的條件。假定在sal列上定義了check約束，並要求sal列值在1000~2000之間，如果不在此範圍，就會提示出錯

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

語法

列名 型別 check(check條件)

在mysql中實現check的功能，一般是在程式中控制或者通過觸發器完成

例子

-- 演示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.練習

商店售貨系統表設計案例

現有一個商店的資料庫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
- 購買數量 nums

建表，在定義中要求宣告[進行合理設計]

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

-- 使用約束的課堂練習

-- 1,建立資料庫
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, -- 購買數量 
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列（整數）

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

-- 自增長演示
-- 建立表
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;

自增長使用細節

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

-- 修改預設的自增長開始值
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;