数据sql设置外键约束

不念不忘少年蓝@ 2023-10-04 17:42 30阅读 0赞

## 概念 ##

*  一张表的一个字段受限于另外一张表的一个字段对应的值。这里涉及到两张表：被引用的表叫主表（父表），另外一张叫从表（子表）。
 *  它们的关系：主从表关系（父子表关系）

*  **子表：**定义了外键的表

外键的取值要么取父表中字段对应的值，要么取NULL值

严重受限于父表

*  **父表：**被引用的字段要具有唯一性（绝大多数都是用的父表的主键）

理论上是可以一张表里的一个字段引用另一个字段，但一般都是用两张表

![90581f8ca13fdc125fe652e69263e773.png][]

## 外键的实现 ##

*  ### **建立表：** ###

先建立父表，后建立子表（因为子表要用到父表）

除非先不考虑外键（建完表后再加），表多的时候可以不考虑建表顺序。弊端：对数据要求很严格，一个垃圾数据都不能放

**父表：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>parent(</code></p> <p><code>　　id number&nbsp;</code><code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>　　</code><code>name</code>&nbsp;<code>varchar2(30),</code></p> <p></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![8a79af47499eb2988e5cd3a8d2802c5f.png][]

**子表：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>child(</code></p> <p><code>　　id number&nbsp;</code><code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>　　</code><code>name</code>&nbsp;<code>varchar2(30),</code></p> <p><code>　　fid number&nbsp;</code><code>constraint</code>&nbsp;<code>child_fid_fk&nbsp;</code><code>references</code>&nbsp;<code>parent(id),</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![fd7f642090f5d6df1e00857832efa5ba.png][]

*  ### **插入数据：** ###

一般先插入父表数据，再插入子表数据。除非把子表的外键值设置成NULL值，否则会出**完整性错误**。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>child&nbsp;</code><code>values</code><code>(1,&nbsp;</code><code>'test1'</code><code>, 1);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![da98fb2f09c60a4174d761aee3cac11b.png][]

要改为：先加入父表数据

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>parent&nbsp;</code><code>values</code><code>(1,&nbsp;</code><code>'p1'</code><code>);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>parent&nbsp;</code><code>values</code><code>(2,&nbsp;</code><code>'p2'</code><code>);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>child&nbsp;</code><code>values</code><code>(1,&nbsp;</code><code>'test1'</code><code>, 1);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![c1223ab9a6ccdce8f27ba101770cce9d.png][]

*  ### 删除数据 ###

子表中有关联的数据，需要先删子表，后删父表。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>parent;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![cc3845e33049b3f642c5fd472e29cb9d.png][]

这里删除父表的id为2的是没关系的，只要没有子表和它关联就行

有关联的必须先删子表的数据，再删父表的数据

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>delete</code>&nbsp;<code>from</code>&nbsp;<code>parent&nbsp;</code><code>where</code>&nbsp;<code>id=1;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![bdfc3724885d5dc307f47cd19107cae2.png][]

*  ### **删除表** ###

先删子表，后删父表

除非使用casecade constraints 解除关联

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>drop</code>&nbsp;<code>table</code>&nbsp;<code>parent;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![cdd061aa00143dae8ea4947604b1b4d7.png][]

先删父表会出错：

有一个被foreign keys关联的unique/primary key的字段在表中

使用casecade constraints解除关联就可以删掉父表

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>drop</code>&nbsp;<code>table</code>&nbsp;<code>parent&nbsp;</code><code>cascade</code>&nbsp;<code>constranints;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![e3efba2804216a156bab001386b1c3b0.png][]

![0bb4e1145fbc5bb82431d3524b5bb7b8.png][]

## 外键的表级约束实现 ##

**父表：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>parent(</code></p> <p><code>　　id number&nbsp;</code><code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>　　</code><code>name</code>&nbsp;<code>varchar2(30),</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![7e8c299716a593cf389fd3f3ac92fa25.png][]

**子表：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>child(</code></p> <p><code>　　id number&nbsp;</code><code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>　　</code><code>name</code>&nbsp;<code>varchar2(30),</code></p> <p><code>　　fid number,</code></p> <p><code>　　</code><code>constraint</code>&nbsp;<code>child_fid_fk&nbsp;</code><code>foreign</code>&nbsp;<code>key</code><code>(fid)&nbsp;</code><code>references</code>&nbsp;<code>parent(id),</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![f0b77922a9dc7c27bc80b93af4b1e44c.png][]

## 先建立表，后加外键 ##

*  **子表s\_emp ：**

里面有外键dept\_id

*  **父表s\_dept**

查看脚本summit2\_drop.sql发现：1270行

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>ALTER</code>&nbsp;&nbsp;<code>TABLE</code>&nbsp;<code>s_emp</code></p> <p><code>ADD</code>&nbsp;&nbsp;<code>CONSTRAINT</code>&nbsp;<code>s_emp_dept_id_fk</code></p> <p><code>FOREIGN</code>&nbsp;&nbsp;<code>KEY</code>&nbsp;<code>(dept_id)&nbsp;&nbsp;</code><code>REFERENCES</code>&nbsp;&nbsp;<code>s_dept(id);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

## 级联删除和级联置空 ##

![6d1d42c0c58b53a3e3c6b6e64bf8f9ac.png][]

*  **级联删除：**

在外键的最后，加上on delete cascade 就是级联删除

再删除主表数据时和主表关联的子表数据也会删除

*  **级联置空**

在在外键的最后，加上on delete set null就是级联置空

再删除主表数据时，会把和主表关联的外键设置成NULL

### 演示：先删表，不管有没有 ###

**（1）建立一张父表部门表**

*  id number primart key
 *  name varchar2（30）

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>mydept(</code></p> <p><code>　　id nimber&nbsp;</code><code>constraint</code>&nbsp;<code>mydept_pk&nbsp;</code><code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>　　</code><code>name</code>&nbsp;<code>varchar2(30),</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![24a7cd17ac04ccf0a156bebd6e067bac.png][]

**(2)建立一张子表 员工表**

*  id number primart key
 *  name varchar2（30）
 *  dept\_id number 外键（关联到部门表）
 *  salary varchar2(30)

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> </td> 
   <td> <p><code>create</code>&nbsp;<code>table</code>&nbsp;<code>mtemp(</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id number&nbsp;</code><code>constraint</code>&nbsp;<code>myepm_id_pk&nbsp;</code><code>primary</code>&nbsp;<code>key</code><code>,</code></p> <p><code>　&nbsp;</code><code>name</code>&nbsp;<code>varchar2(30),</code></p> <p><code>　 salary number,</code></p> <p><code>　 dept_id number&nbsp;</code><code>constraint</code>&nbsp;<code>myemp_depy_id_fk reference mydept(id)&nbsp;</code><code>on</code>&nbsp;<code>delete</code>&nbsp;<code>cascade</code><code>,</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![bff52bba844f06cee2e71a2fa3ed4a28.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>desc</code>&nbsp;<code>mtemp;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![cbe3060aa038110ca6f269b35b0b962b.png][]

**（3）分别向两张表中放入数据**

部门表：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mydept&nbsp;</code><code>values</code><code>(1,&nbsp;</code><code>'test1'</code><code>);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mydept&nbsp;</code><code>values</code><code>(2,</code><code>'test2'</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![751922e0ffaea2fa9de95c62c6ba478b.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>mydept;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![9eeffc31c8efc5ff59199955ade505fd.png][]

员工表：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> </td> 
   <td> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mtemp&nbsp;</code><code>values</code><code>(1,&nbsp;</code><code>'empa'</code><code>, 5000,1);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mtemp&nbsp;</code><code>values</code><code>(2,&nbsp;</code><code>'empb'</code><code>, 4500,1);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mtemp&nbsp;</code><code>values</code><code>(3,&nbsp;</code><code>'empc'</code><code>, 5500,1);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mtemp&nbsp;</code><code>values</code><code>(4,&nbsp;</code><code>'empd'</code><code>, 5800,2);</code></p> <p><code>insert</code>&nbsp;<code>into</code>&nbsp;<code>mtemp&nbsp;</code><code>values</code><code>(5,&nbsp;</code><code>'empe'</code><code>, 5100,2);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![263a55829863d0190e094e953647f7ac.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>mtemp;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![42ed51a4b17271b4be49e86d6b70fcc6.png][]

提交数据：commit；

**（4）因为有on delete cascade，所以删除父表中的id为1的部分是可以的，并不会因为有关联而删不掉。**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>delete</code>&nbsp;<code>from</code>&nbsp;<code>mydept&nbsp;</code><code>where</code>&nbsp;<code>id=1;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![e00da3287a3162d222ca3ec4cd8b8efb.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>mydept;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![d9e7a9620374798b480f3f7a6e14a897.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>mtemp;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![dbf95554cdb25537c7a84eea06407dfd.png][]

**（5）如果换为on delete set null，所以删除父表中的id为1的部分是可以的，而子表的关联的相应部分置为空****　　**

![19c58756b5c60280ee67654ff8754e55.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>mydept;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![08137c693f1a34b83f57d3f3b336ad8d.png][]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>mtemp;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

![5628dd1047a5a1c56e79f227ffd8a100.png][]

**注意：**如果是windows写在.sql脚本里，则所有以上的代码包括commit都写入，再在cmd切到存放脚本的磁盘（如D）。

![e923c3409abc029cc2e41385ace16f4f.png][]

用ftp传到服务器上（192.168.0.26）

![c6dc15a7fb50de217aa3a3fc8b4abd4f.png][]

*  这里清屏用！cls
 *  put 要传送的文件名把文件传送到服务器
 *  @要执行的文件名执行文件
 *  在SQL>@/user/openlab/要执行的脚本文件

[90581f8ca13fdc125fe652e69263e773.png]: https://img-blog.csdnimg.cn/img_convert/90581f8ca13fdc125fe652e69263e773.png
[8a79af47499eb2988e5cd3a8d2802c5f.png]: https://img-blog.csdnimg.cn/img_convert/8a79af47499eb2988e5cd3a8d2802c5f.png
[fd7f642090f5d6df1e00857832efa5ba.png]: https://img-blog.csdnimg.cn/img_convert/fd7f642090f5d6df1e00857832efa5ba.png
[da98fb2f09c60a4174d761aee3cac11b.png]: https://img-blog.csdnimg.cn/img_convert/da98fb2f09c60a4174d761aee3cac11b.png
[c1223ab9a6ccdce8f27ba101770cce9d.png]: https://img-blog.csdnimg.cn/img_convert/c1223ab9a6ccdce8f27ba101770cce9d.png
[cc3845e33049b3f642c5fd472e29cb9d.png]: https://img-blog.csdnimg.cn/img_convert/cc3845e33049b3f642c5fd472e29cb9d.png
[bdfc3724885d5dc307f47cd19107cae2.png]: https://img-blog.csdnimg.cn/img_convert/bdfc3724885d5dc307f47cd19107cae2.png
[cdd061aa00143dae8ea4947604b1b4d7.png]: https://img-blog.csdnimg.cn/img_convert/cdd061aa00143dae8ea4947604b1b4d7.png
[e3efba2804216a156bab001386b1c3b0.png]: https://img-blog.csdnimg.cn/img_convert/e3efba2804216a156bab001386b1c3b0.png
[0bb4e1145fbc5bb82431d3524b5bb7b8.png]: https://img-blog.csdnimg.cn/img_convert/0bb4e1145fbc5bb82431d3524b5bb7b8.png
[7e8c299716a593cf389fd3f3ac92fa25.png]: https://img-blog.csdnimg.cn/img_convert/7e8c299716a593cf389fd3f3ac92fa25.png
[f0b77922a9dc7c27bc80b93af4b1e44c.png]: https://img-blog.csdnimg.cn/img_convert/f0b77922a9dc7c27bc80b93af4b1e44c.png
[6d1d42c0c58b53a3e3c6b6e64bf8f9ac.png]: https://img-blog.csdnimg.cn/img_convert/6d1d42c0c58b53a3e3c6b6e64bf8f9ac.png
[24a7cd17ac04ccf0a156bebd6e067bac.png]: https://img-blog.csdnimg.cn/img_convert/24a7cd17ac04ccf0a156bebd6e067bac.png
[bff52bba844f06cee2e71a2fa3ed4a28.png]: https://img-blog.csdnimg.cn/img_convert/bff52bba844f06cee2e71a2fa3ed4a28.png
[cbe3060aa038110ca6f269b35b0b962b.png]: https://img-blog.csdnimg.cn/img_convert/cbe3060aa038110ca6f269b35b0b962b.png
[751922e0ffaea2fa9de95c62c6ba478b.png]: https://img-blog.csdnimg.cn/img_convert/751922e0ffaea2fa9de95c62c6ba478b.png
[9eeffc31c8efc5ff59199955ade505fd.png]: https://img-blog.csdnimg.cn/img_convert/9eeffc31c8efc5ff59199955ade505fd.png
[263a55829863d0190e094e953647f7ac.png]: https://img-blog.csdnimg.cn/img_convert/263a55829863d0190e094e953647f7ac.png
[42ed51a4b17271b4be49e86d6b70fcc6.png]: https://img-blog.csdnimg.cn/img_convert/42ed51a4b17271b4be49e86d6b70fcc6.png
[e00da3287a3162d222ca3ec4cd8b8efb.png]: https://img-blog.csdnimg.cn/img_convert/e00da3287a3162d222ca3ec4cd8b8efb.png
[d9e7a9620374798b480f3f7a6e14a897.png]: https://img-blog.csdnimg.cn/img_convert/d9e7a9620374798b480f3f7a6e14a897.png
[dbf95554cdb25537c7a84eea06407dfd.png]: https://img-blog.csdnimg.cn/img_convert/dbf95554cdb25537c7a84eea06407dfd.png
[19c58756b5c60280ee67654ff8754e55.png]: https://img-blog.csdnimg.cn/img_convert/19c58756b5c60280ee67654ff8754e55.png
[08137c693f1a34b83f57d3f3b336ad8d.png]: https://img-blog.csdnimg.cn/img_convert/08137c693f1a34b83f57d3f3b336ad8d.png
[5628dd1047a5a1c56e79f227ffd8a100.png]: https://img-blog.csdnimg.cn/img_convert/5628dd1047a5a1c56e79f227ffd8a100.png
[e923c3409abc029cc2e41385ace16f4f.png]: https://img-blog.csdnimg.cn/img_convert/e923c3409abc029cc2e41385ace16f4f.png
[c6dc15a7fb50de217aa3a3fc8b4abd4f.png]: https://img-blog.csdnimg.cn/img_convert/c6dc15a7fb50de217aa3a3fc8b4abd4f.png