Geek asks and answers

Should primary keys always be added to innodb table?

I have several innoDbs with two int columns, which are foreign keys for the primary keys of other tables.

For example, one table is user_items, it has 2 columns, userId, itemId, both foreign keys for user and item tables configured to cascade if they are updated or deleted.

Should I add a third column to such tables and make it the primary key or better, as it is now, in terms of performance or any other benefits?

+5
source share
3 answers

Adding a third identifier column just to add an identifier column does not make sense. In fact, it simply adds processing overhead (index maintenance) when inserting or deleting rows.

The primary key is not necessarily an "identity column".

If you allow only one associated between the user and the element (the user cannot be assigned the same element twice), then it makes sense to define (userid, itemid)as the primary key of your table.

If you allow the same pair to appear more than once, then of course you do not need this restriction.

+6
source

{userId, itemId}. () , .

:

  • FK " ".
  • .
  • -.

, .

, , InnoDB , , , , .

+2

, , , 100 000-500 000 , . created_at updated_at.

, - . , , , . (100 ), , , .

, , , , , . , , , .

One of the best reasons to have a primary key is to give the strings a natural order based on the order in which they were inserted. If you want to get the last 100 (or first 100) rows, it is very simple and fast if you have a primary key with automatic increment in the table.

Adding columns inserted_atand updated_atcan provide similar usefulness in terms of samples on the basis of the date range data. Again, if the number of rows is not very large, it might be worth evaluating them.

0
source

More articles:


All Articles