主键主键外键和索引_主键和外键之间的区别

╰半夏微凉° 2022-12-08 04:44 145阅读 0赞

![47f7a8ec09e0b26e9a6b7ff3c5812ec0.png][]

主键主键外键和索引

A “key” is one of the most basic and important concepts of the Database Management System (DBMS); the keys helps us work easily with the records and the tables in our database by providing us with the ease of accessing and retrieving the data from the table. A primary key is the first key to be understood as it uniquely identifies the particular record of any table. The primary key and foreign key however, must not be confused with each other as they have a completely different definition as well as usage. Here are the differences between the two.

“密钥”是数据库管理系统(DBMS)的最基本和最重要的概念之一； 这些键通过使我们能够轻松地访问和检索表中的数据，从而帮助我们轻松地处理数据库中的记录和表。 主键是第一个要理解的键，因为它唯一地标识任何表的特定记录。 但是，主键和外键不能相互混淆，因为它们的定义和用法完全不同。 这是两者之间的区别。

![Difference between Primary Key and Foreign Key][]

[Image Source][]

[图片来源][Image Source]

## 主键和外键之间的区别 **(**Difference between Primary Key and Foreign Key**)** ##

<table> 
 <tbody> 
  <tr> 
   <td><strong>S.no.</strong></td> 
   <td><strong>Primary key</strong></td> 
   <td><strong>Foreign key</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>The primary key of a particular table is the attribute which uniquely identifies every record and does not contain any null value.</td> 
   <td>The foreign key of a particular table is simply the primary key of some other table which is used as a reference key in the second table.</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>A primary key attribute in a table can never contain a null value.</td> 
   <td>A foreign key attribute may have null values as well.</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Not more than one primary key is permitted in a table.</td> 
   <td>A table can have one or more than one foreign key for referential purposes.</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Duplicity is strictly prohibited in the primary key; there cannot be any duplicate values.</td> 
   <td>Duplicity is permitted in the foreign key attribute, hence duplicate values are permitted.</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Example:<p>Consider the table student, which keeps record of students in a class:</p> <p>Here, Id is the primary key which uniquely identifies each record in the table “student”</p> 
    <table> 
     <tbody> 
      <tr> 
       <td>ID</td> 
       <td>NAME</td> 
       <td>Address</td> 
      </tr> 
      <tr> 
       <td>101</td> 
       <td>ABC</td> 
       <td>x</td> 
      </tr> 
      <tr> 
       <td>102</td> 
       <td>DEF</td> 
       <td>y</td> 
      </tr> 
      <tr> 
       <td>103</td> 
       <td>GHI</td> 
       <td>z</td> 
      </tr> 
      <tr> 
       <td>104</td> 
       <td>JKL</td> 
       <td>w</td> 
      </tr> 
     </tbody> 
    </table> </td> 
   <td>Now, let us consider the table teacher, which records the teachers in the school:<p>Here Tid or teacher id is the unique primary key, for each record in the table. This table uses the “ID” attribute which is the primary key of the “student” table as its reference key or foreign key to refer to the student the particular teacher teaches, :</p> 
    <table> 
     <tbody> 
      <tr> 
       <td>TID</td> 
       <td>NAME</td> 
       <td>TAddress</td> 
       <td>ID</td> 
      </tr> 
      <tr> 
       <td>1</td> 
       <td>Priya</td> 
       <td>P</td> 
       <td>101</td> 
      </tr> 
      <tr> 
       <td>2</td> 
       <td>Riya</td> 
       <td>R</td> 
       <td>102</td> 
      </tr> 
      <tr> 
       <td>3</td> 
       <td>Sam</td> 
       <td>S</td> 
       <td>102</td> 
      </tr> 
      <tr> 
       <td>4</td> 
       <td>Tom</td> 
       <td>T</td> 
       <td>103</td> 
      </tr> 
     </tbody> 
    </table> <p>Since one student is taught by more than one teachers, the ID attribute (which is the foreign key in this table), contains duplicate values.</p></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>序号</strong> </td> 
   <td> <strong>首要的关键</strong> </td> 
   <td> <strong>外键</strong> </td> 
  </tr> 
  <tr> 
   <td> 1个 </td> 
   <td> 特定表的主键是可唯一标识每条记录且不包含任何空值的属性。 </td> 
   <td> 特定表的外键只是其他表的主键，在第二个表中用作参考键。 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 表中的主键属性永远不能包含空值。 </td> 
   <td> 外键属性也可以具有空值。 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> 一个表中最多只能有一个主键。 </td> 
   <td> 一个表可以具有一个或多个外键以供参考。 </td> 
  </tr> 
  <tr> 
   <td> 4 </td> 
   <td> 主键中严格禁止重复； 不能有任何重复的值。 </td> 
   <td> 外键属性中允许重复，因此允许重复值。 </td> 
  </tr> 
  <tr> 
   <td> 5 </td> 
   <td> 例： <p> 考虑一下表格学生，该表格保留班级学生的记录： </p> <p> 在此，Id是唯一标识表“ student”中每个记录的主键 </p> 
    <table> 
     <tbody> 
      <tr> 
       <td> ID </td> 
       <td> 名称 </td> 
       <td> 地址 </td> 
      </tr> 
      <tr> 
       <td> 101 </td> 
       <td> 美国广播公司 </td> 
       <td> X </td> 
      </tr> 
      <tr> 
       <td> 102 </td> 
       <td> 防御 </td> 
       <td> ÿ </td> 
      </tr> 
      <tr> 
       <td> 103 </td> 
       <td> GHI </td> 
       <td> ž </td> 
      </tr> 
      <tr> 
       <td> 104 </td> 
       <td> JKL </td> 
       <td> w </td> 
      </tr> 
     </tbody> 
    </table> </td> 
   <td> 现在，让我们考虑表老师，它记录了学校中的老师： <p> 对于表中的每个记录，此处的Tid或Teacher ID是唯一的主键。 该表使用作为“学生”表主键的“ ID”属性作为其参考键或外键来引用特定老师所教的学生： </p> 
    <table> 
     <tbody> 
      <tr> 
       <td> 工业贸易署 </td> 
       <td> 名称 </td> 
       <td> 地址 </td> 
       <td> ID </td> 
      </tr> 
      <tr> 
       <td> 1个 </td> 
       <td> 普里亚 </td> 
       <td> P </td> 
       <td> 101 </td> 
      </tr> 
      <tr> 
       <td> 2 </td> 
       <td> 里雅 </td> 
       <td> [R </td> 
       <td> 102 </td> 
      </tr> 
      <tr> 
       <td> 3 </td> 
       <td> 山姆 </td> 
       <td> 小号 </td> 
       <td> 102 </td> 
      </tr> 
      <tr> 
       <td> 4 </td> 
       <td> 汤姆 </td> 
       <td> Ť </td> 
       <td> 103 </td> 
      </tr> 
     </tbody> 
    </table> <p> 由于一位学生由多位老师教，因此ID属性(此表中的外键)包含重复的值。 </p> </td> 
  </tr> 
 </tbody> 
</table>

The foreign key of one table although is derived from the primary key of the other table, holds completely different characteristics and these differences are crucial which should be taken care of to avoid any errors.

一个表的外键虽然是从另一表的主键派生而来的，但它们具有完全不同的特征，这些差异至关重要，应注意避免任何错误。

Comment below if you have queries related to primary key vs foreign key.

如果您对主键和外键有疑问，请在下面评论。

> 翻译自: [https://www.thecrazyprogrammer.com/2019/04/difference-between-primary-key-and-foreign-key.html][https_www.thecrazyprogrammer.com_2019_04_difference-between-primary-key-and-foreign-key.html]

主键主键外键和索引

[47f7a8ec09e0b26e9a6b7ff3c5812ec0.png]: /images/20221123/6b68af83d3d94955a8ef1a8f532071bc.png
[Difference between Primary Key and Foreign Key]: /images/20221123/6fee54bc024a44e087c9cd07cfaf0ea2.png
[Image Source]: http://rdbms.opengrass.net/2_Database%20Design/2.1_TermsOfReference/2.1.2_Keys.html
[https_www.thecrazyprogrammer.com_2019_04_difference-between-primary-key-and-foreign-key.html]: https://www.thecrazyprogrammer.com/2019/04/difference-between-primary-key-and-foreign-key.html