The development of the Internet without foreign keys is really a What Do You Mean?

  java, question

It is said that Internet development is as far as possible.No foreign keys, then here’sNo foreign keysWhat exactly does it mean?
There is a clear meaning here:
No foreign key constraints. For example, when deleting data from one table, if you want to Foreign Keys with Cascade Delete the associated data from another table, the database was used to cascade constraints. Now it should be moved to the program to keep the data consistent.

2. Another layer of meaning:
A foreign key is defined as a field in one table that is the primary key in another table. If only according to “Do not use foreign keys“The literal understanding of these words is to extract foreign key fields and put them in oneIntermediate tableChina.In a nutshell, it is all handled as many-to-many pairs.
such as

User (userid,username)
 Orders (orderid,totalPrice,userid)

Question 1: useid is a foreign key here. If there is no foreign key, is it necessary to design it like this?

User (userid,username)
 Orders (orderid,totalPrice)
 User-Order Intermediate Table (userid,orderid)

Question 2: If this is the case, if a table has multiple foreign keys, wouldn’t there be many intermediate tables?

I have searched Baidu all over, saying that foreign keys should not be used, not that foreign keys should not be used as constraints, so I am very confused, and I hope you can greatly dispel your doubts. Thank you very much!

It is said that foreign keys are not used as much as possible in Internet development, so what does the absence of foreign keys mean?

The foreign key here refers to the foreign key constraint of the database.

No foreign key constraints. For example, when deleting data from one table, if you want to Foreign Keys with Cascade Delete the associated data from another table, the database was used to cascade constraints. Now it should be moved to the program to keep the data consistent.

Yes Foreign keys are not maintained by the database but by the application.

A foreign key is defined as a field in one table that is the primary key in another table. If we only follow the literal meaning of the words “do not use foreign keys”, we should extract the foreign key fields and put them in an intermediate table. In a nutshell, it is all handled as many-to-many pairs.

No. How to build a table is still the same as before, except that foreign key constraints are not established where they are needed.

For example, the statement that we originally created the table looks like this:

CREATE TABLE `user` (
 `user _ id`int (11) not null auto _ incrementcomment' primary key',
 `user _ name`varchar (50) not null default'' comment' username',
 PRIMARY KEY (`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 CREATE TABLE `order` (
 `id`int (11) not null auto _ incrementcomment' primary key',
 `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
 `user_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `for_indx_user_id` (`user_id`),
 CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After the foreign key constraint is not used:

CREATE TABLE `user` (
 `user _ id`int (11) not null auto _ incrementcomment' primary key',
 `user _ name`varchar (50) not null default'' comment' username',
 PRIMARY KEY (`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 CREATE TABLE `order` (
 `id`int (11) not null auto _ incrementcomment' primary key',
 `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
 `user_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After foreign key constraints are not applied, we usually add an index to the fields that do not have foreign key constraints to speed up the query.

CREATE TABLE `order` (
 `id`int (11) not null auto _ incrementcomment' primary key',
 `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
 `user_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `idx_user_id` (`user_id`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you understand, your following questions will naturally disappear.

Avoid using foreign keys, and you can maintain constraint relationships through programs when inserting data.

Advantages of using foreign key constraints:

  • Foreign keys can save development
  • Foreign keys can restrict the validity of data, and illegal data cannot be inserted.

Disadvantages of using foreign key constraints:

  • There are extra expenses
  • When the primary key table is locked, it will cause the foreign key table to be locked as well.
  • When deleting the data of the primary key table, the data of the foreign key table must be deleted first
  • When modifying foreign key table fields, foreign key constraints need to be rebuilt

In actual development, foreign key constraints are generally not established.