mysql 表的完整性約束

• 2019 年 10 月 8 日
• 筆記

目錄

• mysql 表的完整性約束
  • 約束概念
  • unsigned 設置某一個數字無符號 (整數類型 ,浮點類型不能是unsigned)
  • not null 某一個欄位不能為空（嚴格模式會影響非空設置的效果）
  • default 給某個欄位設置默認值(設置默認值)
  • unique 設置某一個欄位不能重複 (唯一約束)
  • auto_increment 設置某一個int類型的欄位 自動增加 (自增欄位 必須是數字 且 必須是唯一的)
  • primary key 設置主鍵 (這一個欄位非空且唯一)
  • foreign key 外鍵 (外鍵,涉及到兩張表,數據類型一樣,且有唯一約束)
  • references 級聯刪除和更新

mysql 表的完整性約束

約束概念

為了防止不符合規範的數據進入資料庫，在用戶對數據進行插入、修改、刪除等操作時，DBMS自動按照一定的約束條件對數據進行監測，  使不符合規範的數據不能進入資料庫，以確保資料庫中存儲的數據正確、有效、相容。  約束條件與數據類型的寬度一樣，都是可選參數，主要分為以下幾種：  # NOT NULL ：非空約束，指定某列不能為空；  # UNIQUE : 唯一約束，指定某列或者幾列組合不能重複  # PRIMARY KEY ：主鍵，指定該列的值可以唯一地標識該列記錄  # FOREIGN KEY ：外鍵，指定該行記錄從屬於主表中的一條記錄，主要用於參照完整性

unsigned 設置某一個數字無符號 (整數類型 ,浮點類型不能是unsigned)

#unsigned 就是將數字類型無符號化， 例如 int 型的範圍：-2^31 ~ 2^31 - 1，而unsigned int的範圍：0 ~ 2^32。  not null default create table t2(id int not null,name char(12) not null,age int default 18,  gender enum('male','female') not null default 'male');

not null 某一個欄位不能為空（嚴格模式會影響非空設置的效果）

#not null示例  mysql> create table t12 (id int not null);  Query OK, 0 rows affected (0.02 sec)    mysql> select * from t12;  Empty set (0.00 sec)    mysql> desc t12;  +-------+---------+------+-----+---------+-------+  | Field | Type    | Null | Key | Default | Extra |  +-------+---------+------+-----+---------+-------+  | id    | int(11) | NO   |     | NULL    |       |  +-------+---------+------+-----+---------+-------+  row in set (0.00 sec)    #不能向id列插入空元素。  mysql> insert into t12 values (null);  ERROR 1048 (23000): Column 'id' cannot be null    mysql> insert into t12 values (1);  Query OK, 1 row affected (0.01 sec)    #not null不生效    #設置嚴格模式：      不支援對not null欄位插入null值      不支援對自增長欄位插入值      不支援text欄位有默認值    #直接在mysql中生效(設置在記憶體,重啟失效):  mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";    #配置文件添加(永久生效)：  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

default 給某個欄位設置默認值(設置默認值)

#我們約束某一列不為空，如果這一列中經常有重複的內容，就需要我們頻繁的插入，這樣會給我們的操作帶來新的負擔，  #於是就出現了默認值的概念。  #默認值，創建列時可以指定默認值，當插入數據時如果未主動設置，則自動添加默認值  #not null + default 示例  mysql> create table t13 (id1 int not null,id2 int not null default 222);  Query OK, 0 rows affected (0.01 sec)    mysql> desc t13;  +-------+---------+------+-----+---------+-------+  | Field | Type    | Null | Key | Default | Extra |  +-------+---------+------+-----+---------+-------+  | id1   | int(11) | NO   |     | NULL    |       |  | id2   | int(11) | NO   |     | 222     |       |  +-------+---------+------+-----+---------+-------+  rows in set (0.01 sec)    # 只向id1欄位添加值，會發現id2欄位會使用默認值填充  mysql> insert into t13 (id1) values (111);  Query OK, 1 row affected (0.00 sec)    mysql> select * from t13;  +-----+-----+  | id1 | id2 |  +-----+-----+  | 111 | 222 |  +-----+-----+  row in set (0.00 sec)    # id1欄位不能為空，所以不能單獨向id2欄位填充值；  mysql> insert into t13 (id2) values (223);  ERROR 1364 (HY000): Field 'id1' doesn't have a default value    # 向id1，id2中分別填充數據，id2的填充數據會覆蓋默認值  mysql> insert into t13 (id1,id2) values (112,223);  Query OK, 1 row affected (0.00 sec)    mysql> select * from t13;  +-----+-----+  | id1 | id2 |  +-----+-----+  | 111 | 222 |  | 112 | 223 |  +-----+-----+  rows in set (0.00 sec)

unique 設置某一個欄位不能重複 (唯一約束)

create table t3(id int unique,username char(12) unique,password char(18));    # 聯合唯一  unique(欄位1，欄位2)  create table t4(id int,ip char(15),server char(10),port int,unique(ip,port));

• ?聯合唯一寫法unique( 欄位名,欄位名)

#unique示例  方法一：  create table department1(  id int,  name varchar(20) unique,  comment varchar(100)  );      方法二：  create table department2(  id int,  name varchar(20),  comment varchar(100),  unique(name)  );      mysql> insert into department1 values(1,'IT','技術');  Query OK, 1 row affected (0.00 sec)  mysql> insert into department1 values(1,'IT','技術');  ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'    #not null 和unique的結合  mysql> create table t1(id int not null unique);  Query OK, 0 rows affected (0.02 sec)    mysql> desc t1;  +-------+---------+------+-----+---------+-------+  | Field | Type    | Null | Key | Default | Extra |  +-------+---------+------+-----+---------+-------+  | id    | int(11) | NO   | PRI | NULL    |       |  +-------+---------+------+-----+---------+-------+  row in set (0.00 sec)    #聯合唯一  create table service(  id int primary key auto_increment,  name varchar(20),  host varchar(15) not null,  port int not null,  unique(host,port) #聯合唯一  );    mysql> insert into service values      -> (1,'nginx','192.168.0.10',80),      -> (2,'haproxy','192.168.0.20',80),      -> (3,'mysql','192.168.0.30',3306)      -> ;  Query OK, 3 rows affected (0.01 sec)  Records: 3  Duplicates: 0  Warnings: 0    mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);  ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

auto_increment 設置某一個int類型的欄位 自動增加 (自增欄位 必須是數字 且 必須是唯一的)

• auto_increment自帶not null效果(非空約束)
• 設置條件 int unique
• not null 不能為空 mysql 5.4版本不生效要重新配置 mysql 5.6以上默認生效

create table t5(id int unique auto_increment,username char(10),password char(18));

• ?默認表結構都要有id,一般第一個欄位就是id,且設置自增

primary key 設置主鍵 (這一個欄位非空且唯一)

• 約束這個欄位 非空（not null） 且 唯一（unique）
• 一張表只能由有一個主鍵,一張表最好設置一個主鍵

 primary key(欄位1，欄位2)#主鍵為了保證表中的每一條數據的該欄位都是表格中的唯一值。換言之，  它是用來獨一無二地確認一個表格中的每一行數據。  #主鍵可以包含一個欄位或多個欄位。當主鍵包含多個欄位時，稱為組合鍵 (Composite Key),也可以叫聯合主鍵。  #主鍵可以在建置新表格時設定 (運用 CREATE TABLE 語句)，或是以改變現有的表格架構方式設定 (運用 ALTER TABLE)。  #主鍵必須唯一，主鍵值非空；可以是單一欄位，也可以是多欄位組合。      #你指定的第一個非空且唯一的欄位會被定義成主鍵  create table 表(id int not null unique,name char(12) not null unique);    #設置主鍵方法  create table 表(id int primary key,name char(12) not null unique);    # 聯合主鍵 primary key(欄位1，欄位2)  create table t4(id int,ip char(15),server char(10), port int,primary key(ip,port));

?指定的第一個非空且唯一的欄位會被默認定義成主鍵

?mysql只識別;號為結束語句,換行符不識別,可以執行

foreign key 外鍵 (外鍵,涉及到兩張表,數據類型一樣,且有唯一約束)

• 級聯操作：on update cascade on delete cascade

#多表 ：  #假設我們要描述所有公司的員工，需要描述的屬性有這些 ： 工號 姓名 部門  #公司有3個部門，但是有1個億的員工，那意味著部門這個欄位需要重複存儲，部門名字越長，越浪費  #解決方法： 我們完全可以定義一個部門表 然後讓員工資訊表關聯該表，如何關聯，即foreign key    ###############################創造外鍵的條件#################################  mysql> create table departments (dep_id int(4),dep_name varchar(11));  Query OK, 0 rows affected (0.02 sec)    mysql> desc departments;  +----------+-------------+------+-----+---------+-------+  | Field    | Type        | Null | Key | Default | Extra |  +----------+-------------+------+-----+---------+-------+  | dep_id   | int(4)      | YES  |     | NULL    |       |  | dep_name | varchar(11) | YES  |     | NULL    |       |  +----------+-------------+------+-----+---------+-------+  rows in set (0.00 sec)    # 創建外鍵不成功  mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));  ERROR 1215 (HY000): Cannot add foreign key    # 設置dep_id非空，仍然不能成功創建外鍵  mysql> alter table departments modify dep_id int(4) not null;  Query OK, 0 rows affected (0.02 sec)  Records: 0  Duplicates: 0  Warnings: 0    mysql> desc departments;  +----------+-------------+------+-----+---------+-------+  | Field    | Type        | Null | Key | Default | Extra |  +----------+-------------+------+-----+---------+-------+  | dep_id   | int(4)      | NO   |     | NULL    |       |  | dep_name | varchar(11) | YES  |     | NULL    |       |  +----------+-------------+------+-----+---------+-------+  rows in set (0.00 sec)    mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));  ERROR 1215 (HY000): Cannot add foreign key constraint    # 當設置欄位為unique唯一欄位時，設置該欄位為外鍵成功  mysql> alter table departments modify dep_id int(4) unique;  Query OK, 0 rows affected (0.01 sec)  Records: 0  Duplicates: 0  Warnings: 0    mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+  | Field    | Type        | Null | Key | Default | Extra |  +----------+-------------+------+-----+---------+-------+  | dep_id   | int(4)      | YES  | UNI | NULL    |       |  | dep_name | varchar(11) | YES  |     | NULL    |       |  +----------+-------------+------+-----+---------+-------+  rows in set (0.01 sec)    mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));  Query OK, 0 rows affected (0.02 sec)    #表類型必須是innodb存儲引擎，且被關聯的欄位，即references指定的另外一個表的欄位，必須保證唯一  create table department(  id int primary key,  name varchar(20) not null  )engine=innodb;    #dpt_id外鍵，關聯父表（department主鍵id），同步更新，同步刪除  create table employee(  id int primary key,  name varchar(20) not null,  dpt_id int,  foreign key(dpt_id)  references department(id)  on delete cascade  # 級連刪除  on update cascade # 級連更新  )engine=innodb;      #先往父表department中插入記錄  insert into department values  (1,'教質部'),  (2,'技術部'),  (3,'人力資源部');      #再往子表employee中插入記錄  insert into employee values  (1,'xpn',1),  (2,'gkf',2),  (3,'tiandan',2),  (4,'goudan',2),  (5,'gandan',3),  (6,'敏敏',3),  (7,'皮卡丘',3),  (8,'程咬金',3),  (9,'程咬銀',3)  ;      #刪父表department，子表employee中對應的記錄跟著刪  mysql> delete from department where id=2;    ########################################外鍵操作示例############################################    #更新父表department，子表employee中對應的記錄跟著改  mysql> update department set id=2 where id=3;    ##################################on delete(了解)###################################  . cascade方式  在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄       . set null方式  在父表上update/delete記錄時，將子表上匹配記錄的列設為null  要注意子表的外鍵列不能為not null       . No action方式  如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作       . Restrict方式  同no action, 都是立即檢查外鍵約束       . Set default方式  父表有變更時,子表將外鍵列設置成一個默認的值 但Innodb不能識別

references 級聯刪除和更新

#創建員工表  create table staff(id  int primary key auto_increment,age int,gender  enum('male','female'),salary  float(8,2),hire_date date,post_id int,foreign key(post_id) references post(pid));

作 者：郭楷豐