mysql外鍵約束及表關(guān)系（三）

mysql是關(guān)系型數(shù)據(jù)庫，關(guān)系數(shù)據(jù)庫，是建立在關(guān)系模型基礎(chǔ)上的數(shù)據(jù)庫，
現(xiàn)實(shí)世界中的各種實(shí)體，以及實(shí)體之間的各種聯(lián)系，均用關(guān)系模型（table）來表示。

  • 關(guān)系模型就是指二維表格模型,因而一個(gè)關(guān)系型數(shù)據(jù)庫就是由二維表及其之間的聯(lián)系組成的一個(gè)數(shù)據(jù)組織。
  • 實(shí)體：就是數(shù)據(jù)對象 （可以是有型的，也可以是無型的）；

E-R 圖 （實(shí)體關(guān)系模型）

image.png

E-R圖也稱實(shí)體-聯(lián)系圖(Entity Relationship Diagram)，提供了表示實(shí)體類型、屬性和聯(lián)系的方法，用來描述現(xiàn)實(shí)世界的概念模型。它是描述現(xiàn)實(shí)世界關(guān)系概念模型的有效方法。是表示概念關(guān)系模型的一種方式。

  • 用“矩形框”表示實(shí)體型，矩形框內(nèi)寫明實(shí)體名稱；
  • 用“橢圓圖框”表示實(shí)體的屬性，并用“實(shí)心線段”將其與相應(yīng)關(guān)系的“實(shí)體型”連接起來；
  • 用”菱形框“表示實(shí)體型之間的聯(lián)系成因，在菱形框內(nèi)寫明聯(lián)系名，并用”實(shí)心線段“分別與有關(guān)實(shí)體型連接起來，同時(shí)在”實(shí)心線段“旁標(biāo)上聯(lián)系的類型（1:1,1:n或m:n）。

外鍵約束FOREING KEY

外鍵約束FOREIGN KEY，保持?jǐn)?shù)據(jù)一致性，完整性實(shí)現(xiàn)一對一或一對多關(guān)系。
（因?yàn)橐粋€(gè)表只存一類信息。用外鍵來做參照，保證數(shù)據(jù)的一致性，可以減少數(shù)據(jù)冗余）
外鍵約束的要求：
數(shù)據(jù)表的存儲引擎只能為InnoDB #此點(diǎn)已無需關(guān)注。
外鍵列和參照列數(shù)據(jù)類型一致
外鍵必須關(guān)聯(lián)到鍵上面去，一般情況是關(guān)聯(lián)到，另一張表的主鍵；
--用desc a;查看字段key是否有值，必須關(guān)聯(lián)到key有值的字段；

    #例：
    ##表a
    mysql> create table `a`( 
        -> a_id int primary key,
        -> a_name varchar(20) not null
        -> );
    insert into a values(1,'a1'),(2,'a2');
    
    alter table a modify a_id int auto_increment;
    show create table a;  #因?yàn)榍懊嬉呀?jīng)插入2個(gè)值，這里修改為自增長，則從3開始；
    alter table a auto_increment=6;   # 只能調(diào)大，不可以調(diào)小，一個(gè)表里面只有一個(gè)auto_increment,一般和主鍵一起用。
    insert into a(a_name) value('a6');
        
    ##表b
    create table `b`(
    b_id int primary key,
    b_name varchar(20) not null,
    fy_id int not null,
    constraint AB_id foreign key(fy_id) references `a`(a_id) #fy_id只能取a表a_id字段已有的值，不可以取其他的；constraint可寫可不寫，用來取外鍵名的，不寫則默認(rèn)取的。刪除是通過外鍵名來刪除；
    );
    
    #刪除外鍵
    alter table `b` drop foreign key AB_id;
    
    #增加外鍵
    mysql> alter table `b` 
        -> add constraint AB_id foreign key(fy_id) references `a`(a_id);
    
    #一個(gè)表只存一類信息。

注：
首先，外鍵引用的那個(gè)列在主表中必須是主鍵列或者唯一列。
所以1：n的肯定把外鍵建立在n的那張表上。
1：1，一般要看誰是主表，誰是附屬表，外鍵當(dāng)然建立在附屬表中。
n：m的情況，需要建立一個(gè)關(guān)系表，兩個(gè)原表和其關(guān)系分別是1：n，1：m

一對多關(guān)系（一對多的，一個(gè)表的記錄與另一個(gè)表的多條記錄連接。）

舉例，學(xué)校中一個(gè)學(xué)院可以有很多的學(xué)生，而一個(gè)學(xué)生只屬于某一個(gè)學(xué)院（通常情況下），學(xué)院與學(xué)生之間的關(guān)系就是一對多的關(guān)系，通過外鍵關(guān)聯(lián)來實(shí)現(xiàn)這種關(guān)系。（1：n的在多的一方n建立外鍵，指向1的主鍵）

#例：
##創(chuàng)建學(xué)院表
mysql> create table department( 
    -> d_id int primary key AUTO_INCREMENT,
    -> d_name varchar(20) not null
    -> );
    
##創(chuàng)建學(xué)生表
mysql> create table student(
    -> s_id int primary key AUTO_INCREMENT,
    -> s_name varchar(20) not null,
    -> dept_id int not null,    # 一個(gè)不同列， 關(guān)聯(lián)到另一種表的主鍵
    -> constraint SD_id foreign key(dept_id) references department(d_id)
    -> );


#插入數(shù)據(jù)
mysql> INSERT INTO `department`(`d_name`)
    -> VALUES('計(jì)算機(jī)學(xué)院'),
    ->       ('外語學(xué)院')
    -> ;
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `student`(`s_name`,`dept_id`)
    -> VALUES('s1',1),
    ->       ('s2',2),
    ->       ('s3',2)
    -> ;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

一對一關(guān)系（一個(gè)表的記錄只能與另一個(gè)表的一條記錄連接）

舉例，學(xué)生表中有學(xué)號、姓名、學(xué)院，但學(xué)生還有些比如電話，家庭住址等比較私密的信息，這些信息不會(huì)放在學(xué)生表當(dāng)中，會(huì)新建一個(gè)學(xué)生的詳細(xì)信息表來存放。這時(shí)的學(xué)生表和學(xué)生的詳細(xì)信息表兩者的關(guān)系就是一對一的關(guān)系，因?yàn)橐粋€(gè)學(xué)生只有一條詳細(xì)信息。用外鍵加主鍵的方式來實(shí)現(xiàn)這種關(guān)系。(1：1，一般要看誰是主表，誰是附屬表，外鍵當(dāng)然建立在附屬表中。在附屬表增加一個(gè)主鍵關(guān)聯(lián)從表，增加一個(gè)主鍵關(guān)聯(lián)主表主鍵) 。

#例：
#學(xué)生表：
mysql> DESC `student`;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| dept_id | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)

#建立詳細(xì)學(xué)生表：
mysql> CREATE TABLE `student_details`(
    -> `id` INT PRIMARY KEY,
    -> `sex` VARCHAR(10) not null,
    -> `age` INT,
    -> `address` VARCHAR(20) comment '家庭住址',
    -> `parents` VARCHAR(20),
    -> `home_num` VARCHAR(20),
    -> FOREIGN KEY (`id`) REFERENCES `student`(`s_id`)
    -> );   # 主鍵映射 主鍵
Query OK, 0 rows affected (0.67 sec)

insert into student_details value(2,'男',18,'湖南長沙','佳爸爸','123456789');


insert into student_details value(4,'男',20,'湖南長沙','李爸爸','12541656469');

insert into student_details value(5,'男',20,'湖南長沙','李爸爸','12541656469');

多對多關(guān)系

（多對多的關(guān)系，是通過中間表。中間表是多的一方，所以多的一方要包含指向一的一方的外鍵。所以中間表，作為兩邊多的一方，中間表要包含兩邊的外鍵，兩個(gè)外鍵分別指向兩張表的主鍵。）
舉例，學(xué)生要報(bào)名選修課，一個(gè)學(xué)生可以報(bào)名多門課程，一個(gè)課程有很多的學(xué)生報(bào)名，那么學(xué)生表和課程表兩者就形成了多對多關(guān)系。對于多對多關(guān)系，需要?jiǎng)?chuàng)建第三張關(guān)系表，關(guān)系表中通過外鍵加主鍵的形式實(shí)現(xiàn)這種關(guān)系。（n：m的情況，需要建立一個(gè)關(guān)系表，兩個(gè)原表和其關(guān)系分別是1：n，1：m）

#例：
#建立課程表：
mysql> CREATE TABLE `course`(
    -> `id` INT PRIMARY KEY AUTO_INCREMENT,
    -> `name` VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (1.18 sec)

insert into course(name)  values('python'),('java'),('英語'),('日語');

#學(xué)生與課程多對多關(guān)系表 (中間表)
mysql> CREATE TABLE `select`(
    -> `s_id` INT,  #用來對應(yīng)學(xué)生id
    -> `crs_id` INT, #用來對應(yīng)課程id
    # 防止同一個(gè)學(xué)生，多次選同一門課程
    -> PRIMARY KEY (`s_id`,`crs_id`),
   #  必須學(xué)生表的學(xué)生，才能選課程表中的課程
    -> FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`), 
    # 只有課程存在，才能選
    -> FOREIGN KEY (`crs_id`) REFERENCES `course` (`id`)
    -> );
Query OK, 0 rows affected (0.50 sec)

insert into `select` values(1,1),(1,3),(4,4);
insert into `select` values(2,3),(2,1),(2,4);

外鍵約束的參照操作：

mysql> SELECT * FROM `department`;
+----+----------------------+------+
| d_id | name                 | code |
+-----+----------------------+------+
|  1  | 計(jì)算機(jī)學(xué)院            |    1 |
|  2  | 外國語學(xué)院            |    2 |
+-----+----------------------+------+

mysql> SELECT * FROM `student`;
+----+--------+--------+
| id | name   | dep_id |
+----+--------+--------+
|  1 | budong |      1 |
|  2 | Tuple  |      1 |
|  3 | Which  |      2 |
+----+--------+--------+

mysql> DELETE FROM `department` WHERE `d_id`=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`student`, CONSTRAINT `stu_dep_for_key` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
#因?yàn)橥怄I關(guān)聯(lián)的原因，不能刪除父表中的記錄

mysql> UPDATE `student` SET `dept_id`=1 WHERE `dept_id`=2;
mysql> DELETE FROM `department` WHERE `d_id`=2;
#因?yàn)閟tudent表中的dep_id沒有值關(guān)聯(lián)department中的id=2的數(shù)據(jù)，所以可以刪除department表中id=2的數(shù)據(jù)
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容