In database design, should I design an auto-incremental ID field as the primary key?

I am a newbie. I have been confused for a long time. Today I decided to ask you the correct or common practices. Here is a simple example: id, title, content ,...... Label table: id. The two tables above the title obviously require auto-incrementing IDS as the primary key to ensure uniqueness. Design 1 next... I am a newbie. I am confused about this question for a long time. Today I decided to ask you the correct or common practices.

A simple example

Article table: Id, title, content ,......
Label table: Id, title

The above two tables both require auto-increment IDS as the primary key to ensure uniqueness.

Next, we need to design an association table to associate articles with tags.

Document tag Association Table: Id, Article id, tag id

It can be seen that I only need the following two fields to implement the function. Does this mean that I can discard the auto-increment id field? You can even discard the primary key and simply add an index?

If the auto-increment ID Primary Key is not required, how can I determine whether it is required? Is it determined only by business needs?

Thank you!

Reply content:

I am a newbie. I am confused about this question for a long time. Today I decided to ask you the correct or common practices.

A simple example

Article table: Id, title, content ,......
Label table: Id, title

The above two tables both require auto-increment IDS as the primary key to ensure uniqueness.

Next, we need to design an association table to associate articles with tags.

Document tag Association Table: Id, Article id, tag id

It can be seen that I only need the following two fields to implement the function. Does this mean that I can discard the auto-increment id field? You can even discard the primary key and simply add an index?

If the auto-increment ID Primary Key is not required, how can I determine whether it is required? Is it determined only by business needs?

Thank you!

First, let's talk about the difference between the primary key and the index concept. The primary key is just a declaration. What fields can be used to identify the unique record of the table is implemented only through the unique index.

For most tables in relational databases, we recommend that you have a unique primary key (including the auto-increment ID or UUID method you mentioned), which is within the scope of the logical primary key, mainly for convenience of subsequent program development, especially for Web applications, this Id can be easily passed as a parameter in a hyperlink, for example, editPost. do? Id = 123

In the example of the document tag Association table you mentioned, the associated tag is usually displayed on the document display and editing page, just as segmentfault will display the corresponding tag on the question page, if there is no such id, a tag will be deleted during editing. If it is implemented through an Ajax request, you can follow this url: deleteTagOfPost. do? Id = 123. If it doesn't exist, it will be a lot of trouble. You need to put the values of multiple fields that can be determined for this record in the parameter, such as: deleteTagOfPost. do? PostId = 123 & tagId = 456, it is more convenient to have a logical primary key.

In addition, the combination of multiple fields is used as the primary key to determine the unique record row, which belongs to the category of the physical primary key and is strongly related to the business rules, it is inferior to logical primary keys in convenience and scalability of development.

First, we must make it clear that the primary key is used to uniquely identify a record row. Whether it is a combination of one field or multiple fields, well, a table allows you to set multiple primary keys to uniquely identify a record.

Auto-incrementing IDs are used. For example, articles are all individually counted. An article can be marked with an ID. You can set the initial value and step value for auto-incrementing IDs. Generally, the default value of the initial value and step value is 1, these are caused by table creation. Later programmers do not need to write code for this field separately, which is concise and the INT value is space-saving.

However, auto-increment IDs are not the only primary key. For example, when encountering a large amount of data, unids can also be used as the primary key to ensure that no primary key record is repeated.

You can also use multiple fields as the primary key to determine the unique record row.

It is more convenient to use auto-increment IDs.

First, correct the error

@ Super box

Data cannot be modified without an auto-incrementing primary key ......

Are you sure you want?
As you said, I would like to ask:
1. Is there a primary key auto-increment for the following data?
2. Cannot such data be modified?

Primary Key auto-increment is mainly convenient

Primary Key auto-increment is mainly for convenience, so that the int auto-increment in the database ensures the uniqueness of the primary key. It has nothing to do with whether the data can be modified. As for how to use it, it depends on your personal habits and actual project requirements.

Can't help but do this?

My approach is to let the program generate a primary key by using the timestamp + random number, and attaching the PHP method:

The primary key generated in this way (small projects are used in this way) looks neat. You use auto-increment methods: 1, 2, 3, 4... the primary keys such as 10000 and 3000000000001 look a little strange (I have obsessive-compulsive disorder). Besides, if I delete the primary key with ID 2. It's like this: 1, 3, 4... 10000, 3000000000001; don't you think it looks bad, of course, only obsessive-compulsive disorder.

Summary:

I think that as long as you grasp the uniqueness of the primary key, there are many ways to choose how to generate the primary key. int auto-increment, timestamp + random number, and unique columns can be ensured in table data. This auto-increment int seems to be uncomfortable to many people (my teacher thinks it is inappropriate). Now it seems a bit weird to think about it.
This is my personal opinion. You are welcome to correct it!

From experience, if A meaningful field is used as the primary key of table A and the update operation is involved in the business, the foreign key of the associated table B will become invalid. If you set a foreign key constraint, the database does not allow you to modify this field value.
If a meaningless id is used as the primary key, there is no impact on the logic, but it is much easier to develop.

Personal humanities, id is not a must, but it is a good habit, so it is best to have a and meaningless ID in each of your tables to identify this record.
In addition, IDs are not necessarily auto-incrementing and may have different ID generation policies.

You do not have to add a primary key.

1. A primary key is not necessarily required.
2. The primary key is not necessarily an auto-increment ID.
3. It is more appropriate to use GUID as the primary key.

I think the landlord should first analyze the business needs and decide based on the requirements. The business primary key + GUID is better for the primary key of the database.

You do not have to have an auto-incrementing primary key.
In mysql, innodb recommends that each table have an auto-incrementing primary key.
The auto-increment primary key is used as the focused index of the table.
If there is no auto-incrementing primary key, a unique non-null index will be selected as the primary key index.
Without such an index, innodb will have a hidden rowid as the focus index, which cannot be referenced. (In oracle ).
During SQL queries, the index size slightly affects the query speed.

This is definitely not the case.

The primary key is defined to query the current table.Unique rowTo facilitate subsequent update or deletion operations.

A common way to define a primary key is to increase itself, but it is not necessary to define a self-growth to set it as a primary key;

First, you must understand the meaning of the primary key:A table must define a primary key with only one primary key.;

One primary keyNot equalOne FieldThis must also be understood.

A primary key can be composed of multiple fields to form a primary key.. However, after the combination is required, it must be unique in the current table.

Example:

Single primary key (single field ):For example, a user table is defineduidAs the primary key.

Joint primary key (Multi-field ):
Such as user table, Department table, and user department Association Table
User table: Primary Keyuid;
Department table: Primary Keydepartment_id;
User department Association table:uid,department_idJoint primary key (it is impossible for a person to have two names in the same department)

Data cannot be modified without an auto-incrementing primary key ......

The most intuitive effect is that if you use a useful field as the primary key, the field cannot be null and the field content cannot be modified.