• notice
  • Congratulations on the launch of the Sought Tech site

Should MySQL database remove all foreign key constraints?

Found the necessity of this database foreign key The necessity of
database foreign key, bullshit! At this time, we are still discussing this issue, just like discussing "whether we need to wear clothes and go to the street, or should we all run on the street."

From the main database development trend

It can be seen that large databases are like Orcle, MS SQL Server,...Not to mention, they have always been.

You said that MySql does not support it, that is the MyISAM table format, and now they are all InnoDB table format by default, which supports foreign keys;

You said that SQLite does not support it, but it has been supported since version 3.6.19...

...

...It's just your laziness and habits! ...

Do you like to use foreign keys?

http://www.itpub.net/thread-1313696-1-1.html

Square: Representative newkid.

The main arguments are as follows

The original post was published by newkid at 2010-6-10 22:48.
I remember that I have disputed this issue with others here more than once.
1.No matter how rigorous your program is, there may be bugs ; it is better to leave it to the database to judge by your own judgment, it is done quickly and well.
Most people's programs do not consider concurrency issues. Once considered, it has to be locked manually, which is very inefficient.
Data may bypass your application and enter the database.
2. Performance issues: Does it cost you nothing if you do it yourself?
A foreign key judgment is allocated to the transaction level, the overhead can be ignored, and the user is completely unaware of it.
If you are importing data in batches, you can temporarily block the foreign keys, and use the NOVALIDATE option to quickly restore them afterwards, provided that your data is clean.
3.Show me a troublesome example?
The foreign key constraint is to prevent you from messing around.This is your protection.
Is it troublesome to fasten your seat belt? Sometimes it can save your life.
4.Nyfor said that delay constraints can be used. But according to my experience, it is not necessary. If there is a foreign key, the parent data must be generated first. For example, if you need this order number for your warehousing list, the warehousing details must be generated first.
In addition, the foreign key will provide important information to the CBO to generate the optimal plan.

Counter: Representative Qingyun.

The main arguments are as follows

The original post was published by qingyun at 2010-6-10 13:32.
I don’t like foreign keys.
Reasons:
1. Program logic, integrity , I will make strict judgments in stored procedures or packages, etc.;
2. Performance issues, This is the key reason I don’t like to use it.For example, a business flow table that frequently inserts data.If there are 3 foreign keys on this table, then every time you insert one, you must do it for the 3 tables corresponding to the 3 foreign keys.Corresponding search to determine whether there is corresponding data, if these three tables are also very large, then the judgment time of these three tables is very common, although the field of the associated table pointed to by the foreign key is definitely an index, but I think many times, This kind of judgment is originally controlled in the program, and it is judged by the foreign key again, which is to reduce the performance; and in fact, it does not matter whether the judgment is judged in some places, but when the foreign key is used, it must take time to judge, regardless of oracle How to optimize the internal foreign key for data retrieval speed, it is always a big consumption;
3. Maintenance is troublesome , many companies’ software is customized, this customized thing is relatively random, and the project development and implementation process In the middle, the table needs to be repaired frequently; there are bugs or other situations in the business logic, and the data needs to be maintained manually, and there are intricate foreign key associations, which is very troublesome;
4. Inflexible , the foreign key is deadlocked.The sequence of data generation relationship between the tables, the most common is the document master-slave table, sometimes, when the document is generated, the details are generated first, and then the master table; if the foreign key is nailed, this will not work.Realization;
Of course, some key businesses do need foreign keys;
why do I talk about this topic? After I established the project database today, I used very few foreign keys.My colleagues around me said that the data is not rigorous, and the complicated ones with the same field names are needed.Foreign keys have been established, this I am very reluctant;
For example, this database has a total of 100 tables.According to their ideas, there may be 300 foreign keys; I am dizzy, too dogmatic;
if I were to build 300 indexes, I would be happy, because it improves operation efficiency.The key is only for the rigor of testing, and it only reduces the operational efficiency of the database, without any possibility of improvement;
in fact, this is just a matter of design habits, and if you are interested, you can just chat about your habits.

There is also an opposing voice with unique insights

Put it together for your reference

The original post was published by ruideliang at 2010-6-12 14:42.The
foreign key is exposed, the program is closed, it is also a tested program and foreign key constraints.The possibility of constraint failure caused by human factors is big or small, very Obviously, it is against the use of foreign keys, because it conflicts with the purpose of system high availability

opposite!

Are foreign keys exposed? As long as you bypass your application and log in to the database, what else is not exposed?
Do you want a primary key? It is also exposed, you can DROP at any time.
What about the table structure? Is it exposed? You can modify it at any time.
What does this have to do with high availability?
"It is also a tested program and foreign key constraints ":
Foreign keys do not need to be tested. As long as your data has such a relationship, a DDL can be done.
Foreign keys can also be closed. There is something called "authorization", users who shouldn't see can't see anything! In the system I designed, the client can only see the interface of the stored procedure.

Design principles of primary keys and foreign keys in databases

http://www.cnblogs.com/tianyamoon/archive/2008/04/02/1134394.html

Primary keys and foreign keys are the glue that organizes multiple tables into an effective relational database. The design of primary keys and foreign keys has a decisive influence on the performance and availability of the physical database.

The database model must be transformed from a theoretical logical design to an actual physical design. The structure of the primary key and foreign key is the crux of this design process. Once the designed database is used in the production environment, it is difficult to modify these keys, so it is very necessary and worthwhile to design the primary key and foreign key in the development stage.

Primary key:

Relational databases rely on the primary key --- it is the cornerstone of the physical model of the database. The primary key has only two uses at the physical level:

  1. Uniquely identify a row.

  2. As an object that can be effectively referenced by foreign keys .

Based on the above two purposes, here are some principles I followed when designing the primary key at the physical level:

  1. The primary key should be meaningless to the user. If a user sees data in a join table that represents a many-to-many relationship and complains that it is useless, it proves that its primary key is well designed.

  2. The primary key should be a single column in order to improve the efficiency of join and filter operations.
    Note: People who use composite keys usually have two reasons to excuse themselves, and both of these reasons are wrong. One is that the primary key should have practical meaning.However, making the primary key meaningful is nothing more than a convenience for artificially destroying the database. The second is that this method can use two foreign keys as the primary key in the join table describing the many-to-many relationship.I also oppose this approach.The reason is: composite primary keys often lead to bad foreign keys, that is, when joining tables Become the primary table of another secondary table, and become part of the primary key of this table according to the second method above.However, this table may become the primary table of other secondary tables, and its primary key may become other secondary tables.Part of the primary key is passed in this way, and the lower the secondary table, the more columns its primary key will contain.

  3. Never update the primary key. In fact, because the primary key has no other purpose except to uniquely identify a row, there is no reason to update it. If the primary key needs to be updated, the principle that the primary key should be meaningless to the user has been violated.
    Note: This principle does not apply to data that often needs to be sorted out during data conversion or multi-database merging.

  4. The primary key should not contain dynamically changing data, such as timestamp, creation time column, modification time column, etc.

  5. The primary key should be automatically generated by the computer. If someone intervenes in the creation of the primary key, it will have a meaning other than a unique line. Once this boundary is crossed, there may be a motivation to modify the primary key, so that the key means used by this system to link and manage records will fall into the hands of people who do not understand the database design.


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+