Difference between primary key and foreign key in DBMS

Keys are a key part of the DBMS, they are used to identify and establish relationships between tables in the schema. The following article will introduce you to the two very important keys of DBMS, namely primary keys and foreign keys; introduce the difference between primary keys and foreign keys, I hope it will be helpful to you.

Primary Key in DBMS

The primary key uniquely defines the tuples in the relationship. It can be a single attribute in a relationship or a set of attributes in a relationship. The value of a primary key attribute should not change or rarely change. Because it is the principal, it is meant to identify any record in the database; a change in the value of any attribute of the primary key will cause confusion. [Video tutorial recommendation: MySQL tutorial]

Any relationship can only have one primary key. Primary keys are cluster-indexed by default, which means that all tuples in the table are ordered based on the primary key attribute value. Primary key constraints can be defined on temporary tables. Intermediate tables created during query execution are called temporary tables.

Foreign key in DBMS

Foreign key is a key that refers to the primary key of another relationship; when relationship R1 in its attributes has other When the attribute is the primary key of relation R2, the attribute is called the foreign key of relation R1. The relationship R1 containing the foreign key is called a reference relationship because it refers to the primary key of relationship R2, and the relationship R2 is called a reference relationship.

Unlike the primary key, the foreign key can accept NULL values ​​because it does not explicitly identify the record's task in the relationship; similarly, the foreign key also accepts duplicate values.

A relationship can have multiple foreign keys because it can have different attributes that are primary keys in different relationships. Foreign key constraints cannot be defined on temporary tables, and foreign keys are not clustered index properties.

The main difference between primary key and foreign key

1. Essentially different

A primary key is a candidate key chosen that uniquely defines a tuple in a relationship; a foreign key in a table refers to the primary key of another table.

2. NULL value

The primary key value can never be NULL; foreign keys accept NULL values.

3. Duplicate values ​​

There are no two tuples in the primary key relationship that carry duplicate values ​​for the primary key attribute. Tuples in foreign keys can carry duplicate values ​​for foreign key attributes.

4. Range

The relationship can only have one primary key. There can be multiple foreign keys in a relationship.

5. Temporary table

Primary key You can define primary key constraints on the temporary table. Foreign Keys Foreign key constraints cannot be defined on temporary tables.

6. Clustered index

By default, the primary key is a clustered index. Foreign keys cannot automatically cluster indexes, it must be done manually.

7. Insertion

In the primary key, we can insert a value into the primary key attribute, even if the referring foreign key does not have the value in its column.

In a foreign key, if the value does not exist in the referenced primary key column, the value cannot be inserted into the foreign key.

8. Delete

Before deleting the primary key value, please ensure that the value still does not exist in the referenced foreign key column of the reference table. We can delete a value from a foreign key column without worrying whether the value exists in the referencing primary key column of the referencing relationship.

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of Difference between primary key and foreign key in DBMS. For more information, please follow other related articles on the PHP Chinese website!