07@mysql資料庫表的完整性限制(primary key、foreign key等)

文章目錄

mysql資料庫的完整性限制

一、完整性限制介紹

限制條件：

二、not null與default（預設）
三、unique(唯一限制)

1、單個設定unique
2、多個聯合設定unique

四、primary key（主鍵限制）

1、單列做主鍵
2、多列做主鍵
3、删除主鍵限制

五、auto_increment(自動增長)

1、主鍵限制，設定自動增長
2、修改（主鍵）自動增長

六、 foreign key（外鍵限制）

1、foreign key表的對應關系
2、 foreign key（外鍵限制）的使用
3、建立表之間的關系

1)一對多(單項foreign key)
2)一對一（unique+foreign key）
3)多對多（建立中間表，雙向的foreign key）
4）測試練習

mysql資料庫的完整性限制

一、完整性限制介紹

完整性限制是保證使用者對資料庫所做的修改不會破壞資料的一緻性，是保護資料正确性和相容性的一種手段

限制條件與資料類型的寬度是一樣，都是可以選擇的

限制條件：

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 必須為正值（無符号） 不允許為空 預設是20

3. 是否是key
主鍵 primary key
外鍵 foreign key
索引 (index,unique...)

二、not null與default（預設）

not null (不可空)

null （可空）

#不設定，預設為空，插入時，可插入空
==================not null====================
mysql> create table t1(id int);       #建立t1，id字段未指定，預設可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values();       #插入時可以插入空





#設定不為空（not null）時，插入時不能為空
mysql> create table t2(id int not null);  #設定字段id不為空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values();         #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value








#設定為default（預設）值1，插入為id為空時，預設為1，則為插入值
==================default====================
#設定id字段有預設值後，則無論id字段是null還是not null，都可以插入空，插入空預設填入default指定的預設值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;











==================綜合練習====================
mysql> create table student(
    -> name varchar(20) not null,                   
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );
mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(3) unsigned                    | NO   |     | 18         |       |
| sex   | enum('male','female')              | YES  |     | male       |       |
| hobby | set('play','study','read','music') | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('egon');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| egon |  18 | male | play,music |

三、unique(唯一限制)

1、單個設定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)
);



#插入字段資料
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（id不為空，且唯一）
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    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

2、多個聯合設定unique

#建立
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port)     #表示host與prot聯合唯一
);

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'

四、primary key（主鍵限制）

主鍵primary key是innodb存儲引擎組織資料的依據，innodb稱之為索引組織表，一張表中必須有且隻有一個主鍵

#一個表中可以：

1、單列做主鍵

============單列做主鍵===============
#方法一：not null+unique
create table department1(
id int not null unique,          #使用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 key
create table department2(
id int primary key,    #使用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 key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id);     #建立主鍵并為其命名pk_name

mysql> 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)

2、多列做主鍵

==================多列做主鍵================
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: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

3、删除主鍵限制

#删除表中已有的主鍵限制
（1）無命名
select * from user_cons_columns;           #查找表中主鍵名稱得student表中的主鍵名為id
alter table student drop constraint id;    #删除主鍵限制
（2）有命名
alter table students drop constraint yy;


#向表中添加主鍵限制
alter table student add constraint pk_student primary key(studentid);

五、auto_increment(自動增長)

限制字段為自動增長，被限制的字段必須同時被key限制

1、主鍵限制，設定自動增長

#建立時，不指定id，則自動增長
create table student(
id int primary key auto_increment,     #設定主鍵限制，自動增長
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;        #檢視表結構
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+




#插入時，也可以指定id
mysql> insert into student values(4,'asb','female');     #插入時，指定id
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+






#對于自增的字段，在用delete删除後，再插入值，該字段仍按照删除前的位置繼續增長（id按照之前的表結構繼續增加）
mysql> delete from student;             #删除所有字段
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');   #插入字段
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |          #字段id按照之前的增長
+----+------+------+






#對于無法清空現象，可以使用truncate清空表，delete是一條一條地删除記錄，truncate是直接清空表（在删除大表，清空時用truncate）
mysql> truncate student;          #truncate是直接清空表
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');    
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |         #删除後，插入字段時，重1開始增長
+----+------+------+
1 row in set (0.00 sec)

2、修改（主鍵）自動增長

#在建立完表後，修改自增字段的起始值
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=utf8




mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  3 | egon | 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=utf8

mysql自增的步長：
    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=2




mysql> 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('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+


#步長:auto_increment_increment
#起始偏移量:auto_increment_offset

六、 foreign key（外鍵限制）

1、foreign key表的對應關系

#分析步驟：
1）#先站在左表的角度去找
  是否左表的多條記錄可以對應右表的一條記錄，如果是，則證明左表的一個字段foreign key 右表一個字段（通常是id）

2）#再站在右表的角度去找
是否右表的多條記錄可以對應左表的一條記錄，如果是，則證明右表的一個字段foreign key 左表一個字段（通常是id）



#foreign key的對應關系
#多對一：
如果隻有步驟1成立，則是左表多對一右表
如果隻有步驟2成立，則是右表多對一左表

#多對多
如果步驟1和2同時成立，則證明這兩張表時一個雙向的多對一，即多對多,需要定義一個這兩張表的關系表來專門存放二者的關系

#一對一:

2、 foreign key（外鍵限制）的使用

#建立foreign key：
create table 表名(
    屬性名1 字段類型1 是否可以為空值（不可以需要書寫not null）,
    屬性名2 字段類型2 ...,
    ...     ...     ...,
    constraint 限制名稱 foreign key(屬性名) references 表名(屬性名)
 )；


#已存在的表添加foreign key

alter table 表名 add constraint 限制名稱 foreign key(屬性名) references 表名(屬性名)



#删除外鍵限制

3、建立表之間的關系

1)一對多(單項foreign key)

三張表：出版社，作者資訊，書 

一對多（或多對一）：一個出版社可以出版多本書

關聯方式：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 cascade     #級聯更新
on update cascade     #級聯删除
);


insert into press(name) values
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識産權沒有用出版社')
;

insert into book(name,press_id) values
('九陽神功',1),
('九陰真經',2),
('九陰白骨爪',2),
('獨孤九劍',3),
('降龍十巴掌',2),
('葵花寶典',3)
;

2)一對一（unique+foreign key）

兩張表：學生表和客戶表

一對一：一個學生是一個客戶，一個客戶有可能變成一個學校，即一對一的關系

關聯方式：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) #外鍵的字段一定要保證unique
on delete cascade      #級聯删除
on update cascade      #級聯更新
);


#增加客戶
insert into customer(name,qq,phone) values
('李飛機','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴彈','283818181',1867141331),
('吳坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('戰地雷','112312312',18811431230)
;


#增加學生
insert into student(class_name,customer_id) values
('脫産3班',3),
('周末19期',4),
('周末19期',5)
;

3)多對多（建立中間表，雙向的foreign key）

三張表：出版社，作者資訊，書

多對多：一個作者可以寫多本書，一本書也可以有多個作者，雙向的一對多，即多對多
　　
關聯方式：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 cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);


#插入四個作者，id依次排開
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每個作者與自己的代表作如下
1 egon: 
      1 九陽神功
      2 九陰真經
      3 九陰白骨爪
      4 獨孤九劍
      5 降龍十巴掌
      6 葵花寶典


2 alex: 
      1 九陽神功
      6 葵花寶典

3 yuanhao:
      4 獨孤九劍
      5 降龍十巴掌
      6 葵花寶典

4 wpq:
      1 九陽神功


insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

4）測試練習

案列：

關系建立：

# 班級表，直接建立
create table class(
cid int primary key auto_increment,
caption varchar(10)
);

# 老師表，直接建立
create table teacher(
tid int primary key auto_increment,
tname varchar(16)
);


# 學生表，sid唯一，gender（枚舉類型enum），class_id(多個學生對應一個班)
create table student(
sid int primary key auto_increment,
sname varchar(16),
gender enum("男","女"),
class_id int,
foreign key(class_id) references class(cid)
on delete cascade
on update cascade
);



# 課程表，cid唯一，cname(字元串)，tearch_id(一個老師對應一節課)
create table course(
cid int primary key auto_increment,
cname varchar(16),
tearch_id int,
foreign key(tearch_id) references teacher(tid)
on delete cascade
on update cascade
);


# 成績表，sid唯一，student_id(int),course_id(int),number(int)
create table score(
sid int primary key auto_increment,
student_id int,
foreign key(student_id) references student(sid),
corse_id int,
foreign key(corse_id) references course(cid),
number int
);

07@mysql資料庫表的完整性限制(primary key、foreign key等)

文章目錄

mysql資料庫的完整性限制

一、完整性限制介紹

限制條件：

二、not null與default（預設）

三、unique(唯一限制)

1、單個設定unique

2、多個聯合設定unique

四、primary key（主鍵限制）

1、單列做主鍵

2、多列做主鍵

3、删除主鍵限制

五、auto_increment(自動增長)

1、主鍵限制，設定自動增長

2、修改（主鍵）自動增長

六、 foreign key（外鍵限制）

1、foreign key表的對應關系

2、 foreign key（外鍵限制）的使用

3、 建立表之間的關系

1)一對多(單項foreign key)

2)一對一（unique+foreign key）

3)多對多（建立中間表，雙向的foreign key）

4）測試練習

繼續閱讀

3、建立表之間的關系