=======MySQL表完整性約束======目錄：一、介紹二、not null 與 default三、unique四、primary key五、auto_increment六、foreign key一、介紹約束條件與數據類型的寬度一樣，都是可選參數作用：用于保證數據的完整性和一致性主要分為：PRIMARY KEY (PK) 標識該字段為表的主鍵，可以唯一的標識記錄FOREIGN KEY (FK) 標識該字段為該表的外鍵NOT NULL 標識該字段不能為空UNIQUE KEY (UK) 標識該字段是唯一值AUTO_INCREMENT 標識該字段的值自動增長（整數類型，而且為主鍵）DEFAULT 為該字段設置默認值UNSIGNED 無符號ZEROFILL 使用0填充說明：1. 是否允許為空，默認NULL，可設置NOT NULL，字段不允許為空，必須賦值2. 字段是否有默認值，缺省的默認值是NULL，如果插入記錄時不給字段賦值，此字段使用默認值sex enum('male','female') not null default 'male'age int unsigned NOT NULL default 20 必須為正值（無符號） 不允許為空 默認是203. 是否是key主鍵 primary key外鍵 foreign key索引 (index,unique...)二、not null與default是否為空,null表示空,非字符串not null -不可空null -可空默認值，創建列時可以指定默認值，當插入數據時如果未主動設置，則自動添加默認值create table tb1(nid int not null defalut 2,num int not null)三、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),constraint uk_name unique(name) #為唯一索引起名字);注：唯一聯合create table service(id int primary key auto_increment,name varchar(20),host varchar(15) not null,port int not null,unique(host,port) #聯合唯一);四、primary從約束角度看primary key字段的值不為空且唯一，那我們直接使用not null+unique不就可以了嗎，要它干什么？主鍵primary key是innodb存儲引擎組織數據的依據，innodb稱之為索引組織表，一張表中必須有且只有一個主鍵。一個表中可以：單列做主鍵多列做主鍵（復合主鍵）- 單列做主鍵============單列做主鍵===============#方法一：not null+uniquecreate table department1(id int not null unique, #主鍵name varchar(20) not null unique,comment varchar(100));mysql> desc department1;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(20) | NO | UNI | NULL | || comment | varchar(100) | YES | | NULL | |+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)#方法二：在某一個字段后用primary keycreate table department2(id int primary key, #主鍵name varchar(20),comment varchar(100));mysql> desc department2;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | || comment | varchar(100) | YES | | NULL | |+---------+--------------+------+-----+---------+-------+rows in set (0.00 sec)#方法三：在所有字段后單獨定義primary keycreate table department3(id int,name varchar(20),comment varchar(100),constraint pk_name primary key(id); #創建主鍵并為其命名pk_namemysql> desc department3;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | || comment | varchar(100) | YES | | NULL | |+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)- 多列做主鍵==================多列做主鍵================create table service(ip varchar(15),port char(5),service_name varchar(10) not null,primary key(ip,port));mysql> desc service;+--------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| ip | varchar(15) | NO | PRI | NULL | || port | char(5) | NO | PRI | NULL | || service_name | varchar(10) | NO | | NULL | |+--------------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into service values-> ('172.16.45.10','3306','mysqld'),-> ('172.16.45.11','3306','mariadb')-> ;Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into service values ('172.16.45.10','3306','nginx');ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'五、auto_increment- 約束字段為自增長，被約束的字段必須同時被key約束- 不指定id，則自動增長- 也可以指定id- 對于自增的字段，在用delete刪除后，再插入值，該字段仍按照刪除前的位置繼續增長- 應該用truncate清空表，比起delete一條一條地刪除記錄，truncate是直接清空表，在刪除大表時用它- 步長:auto_increment_increment,起始偏移量:auto_increment_offset#在創建完表后，修改自增字段的起始值mysql> create table student(-> id int primary key auto_increment,-> name varchar(20),-> sex enum('male','female') default 'male'-> );mysql> alter table student auto_increment=3;mysql> show create table student;.......ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mysql> insert into student(name) values('jake');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+------+------+| id | name | sex |+----+------+------+| 3 | jake | male |+----+------+------+row in set (0.00 sec)mysql> show create table student;.......ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8#也可以創建表時指定auto_increment的初始值，注意初始值的設置為表選項，應該放到括號外create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male')auto_increment=3;#設置步長sqlserver：自增步長基于表級別create table t1（id int。。。）engine=innodb,auto_increment=2 步長=2 default charset=utf8mysql自增的步長：show session variables like 'auto_inc%';#基于會話級別set session auth_increment_increment=2 #修改會話級別的步長#基于全局級別的set global auth_increment_increment=2 #修改全局級別的步長（所有會話都生效）#！！！注意了注意了注意了！！！If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 翻譯：如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值會被忽略 ，這相當于第一步步子就邁大了，扯著了蛋比如：設置auto_increment_offset=3，auto_increment_increment=2mysql> set global auto_increment_increment=5;Query OK, 0 rows affected (0.00 sec)mysql> set global auto_increment_offset=3;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'auto_incre%'; #需要退出重新登錄+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset | 1 |+--------------------------+-------+create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');mysql> insert into student(name) values('jake1'),('jake2'),('jake3');mysql> select * from student;+----+-------+------+| id | name | sex |+----+-------+------+| 3 | jake1 | male || 8 | jake2 | male || 13 | jake3 | male |+----+-------+------+六、foreign key- 快速理解foreign key員工信息表有三個字段：工號 姓名 部門公司有3個部門，但是有1個億的員工，那意味著部門這個字段需要重復存儲，部門名字越長，越浪費解決方法：我們完全可以定義一個部門表然后讓員工信息表關聯該表，如何關聯，即foreign key#表類型必須是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,constraint fk_name foreign key(dpt_id)references department(id)on delete cascadeon update cascade )engine=innodb;#先往父表department中插入記錄#再往子表employee中插入記錄#刪父表department，子表employee中對應的記錄跟著刪#更新父表department，子表employee中對應的記錄跟著改- 如何找出兩張表中的關系分析步驟：#1、先站在左表的角度去找是否左表的多條記錄可以對應右表的一條記錄，如果是，則證明左表的一個字段foreign key 右表一個字段（通常是id）#2、再站在右表的角度去找是否右表的多條記錄可以對應左表的一條記錄，如果是，則證明右表的一個字段foreign key 左表一個字段（通常是id）#3、總結：#多對一：如果只有步驟1成立，則是左表多對一右表如果只有步驟2成立，則是右表多對一左表#多對多如果步驟1和2同時成立，則證明這兩張表時一個雙向的多對一，即多對多,需要定義一個這兩張表的關系表來專門存放二者的關系#一對一:如果1和2都不成立，而是左表的一條記錄唯一對應右表的一條記錄，反之亦然。這種情況很簡單，就是在左表foreign key右表的基礎上，將左表的外鍵字段設置成unique即可- 建立表之間的關系#一對多或稱為多對一三張表：出版社，作者信息，書一對多（或多對一）：一個出版社可以出版多本書關聯方式：foreign key=====================多對一=====================create table press(id int primary key auto_increment,name varchar(20));create table book(id int primary key auto_increment,name varchar(20),press_id int not null,foreign key(press_id) references press(id)on delete cascadeon update cascade);#多對多三張表：出版社，作者信息，書多對多：一個作者可以寫多本書，一本書也可以有多個作者，雙向的一對多，即多對多關聯方式：foreign key+一張新的表=====================多對多=====================create table author(id int primary key auto_increment,name varchar(20));#這張表就存放作者表與書表的關系，即查詢二者的關系查這表就可以了create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,constraint fk_author foreign key(author_id) references author(id)on delete cascadeon update cascade,constraint fk_book foreign key(book_id) references book(id)on delete cascadeon update cascade,primary key(author_id,book_id));#一對一兩張表：學生表和客戶表一對一：一個學生是一個客戶，一個客戶有可能變成一個學校，即一對一的關系關聯方式：foreign key+unique=====================一對一=====================#一定是student來foreign key表customer，這樣就保證了：#1 學生一定是一個客戶，#2 客戶不一定是學生，但有可能成為一個學生create table customer(id int primary key auto_increment,name varchar(20) not null,qq varchar(10) not null,phone char(16) not null);create table student(id int primary key auto_increment,class_name varchar(20) not null,customer_id int unique, #該字段一定要是唯一的foreign key(customer_id) references customer(id) #外鍵的字段一定要保證uniqueon delete cascadeon update cascade);

?

轉載于:https://www.cnblogs.com/xiao-xue-di/p/9672810.html

超強干貨來襲 云風專訪：近40年碼齡，通宵達旦的技術人生

總結

以上是生活随笔為你收集整理的MySQL表完整性约束的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。