Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the difference between primary keys and foreign keys in DBMS, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Primary key in DBMS

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

Any relationship can have only one primary key. The primary key defaults to cluster-indexed, which means that all tuples in the table are sorted by the primary key attribute value. You can define primary key constraints on temporary tables. Intermediate tables created during query execution are called temporary tables.

Foreign keys in DBMS

A foreign key is a key that references the primary key of another relationship; when relation R1 in its attribute has the primary key of another relationship R2, the attribute is called the foreign key of relationship R1. Relationship R1, which contains foreign keys, is called a reference relationship because it references the primary key of relationship R2, which is called a reference relationship.

Unlike the primary key, the foreign key can accept a null value because it does not explicitly identify the task of the record in the relationship; similarly, the foreign key accepts duplicate values.

A relationship can have multiple foreign keys because it can have different properties, which are primary keys in different relationships. Cannot define a foreign key constraint on a temporary table, and the foreign key is not a cluster index property.

Main differences between primary and foreign keys

1. Different in nature

The primary key is the selected candidate key, which uniquely defines the tuples in the relationship; the foreign key in the table refers to the primary key of other tables.

2. Null value

Primary key values can never accept null values for NULL; foreign keys.

3. Duplicate value

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

4. Scope

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

5. Temporary table

Primary keys can define primary key constraints on temporary tables. Foreign keys cannot define foreign key constraints on temporary tables.

6. Clustered index

By default, the primary key is clustered indexed. The foreign key cannot automatically clustered the index, it must be done manually.

7. Insert

In the primary key, we can insert a value into the primary key property, even if the reference foreign key does not have that 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, make sure that the value still does not exist in the referenced foreign key column of the referenced table. We can remove the value from the foreign key column without worrying about whether the value exists in the reference primary key column of the reference relationship.

Thank you for reading this article carefully. I hope the editor will share what is the difference between the primary key and the foreign key in DBMS. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.