|
MySQL - Reasons NOT to Use Foreign Keys constraints
MySQL and SQL
(Continued from previous question...)
MySQL - Reasons NOT to Use Foreign Keys constraints
There are so many problems with foreign key constraints that we don't know where to start:
Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and
implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied, and removed.
The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY constraint checks are
useless because you usually insert records in the right tables in the right order, anyway.
There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through
the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other
tables.
You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or
from a backup).
If you use foreign key constraints you can't dump and restore tables unless you do so in a very specific order.
It's very easy to do ``allowed'' circular definitions that make the tables impossible to re-create each table with a single
create statement, even if the definition works and is usable.
It's very easy to overlook FOREIGN KEY ... ON DELETE rules when one codes an application. It's not unusual that one loses a
lot of important information just because a wrong or misused ON DELETE rule.
The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is
connected and to use this to show connection diagrams and to help in building applicatons.
MySQL will soon store FOREIGN KEY definitions so that a client can ask for and receive an answer about how the original
connection was made. The current `.frm' file format does not have any place for it. At a later stage we will implement the
foreign key constraints for application that can't easily be coded to avoid them.
(Continued on next question...)
Other Interview Questions
- What's MySQL
- What is DDL, DML and DCL?
- How do you get the number of rows affected by query?
- If the value in the column is repeatable, how do you find out the unique values?
- How do you return the a hundred books starting from 25th?
- You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
- How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
- How would you select all the users, whose phone number is null?
- What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)
- How do you find out which auto increment was assigned on the last insert?
- What does –i-am-a-dummy flag to do when starting MySQL?
- On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?
- When would you use ORDER BY in DELETE statement?
- How can you see all indexes defined for a table?
- How would you change a column from VARCHAR(10) to VARCHAR(50)?
- How would you delete a column?
- How would you change a table to InnoDB?
- When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
- How do I find out all databases starting with ‘tech’ to which I have access to?
- How do you concatenate strings in MySQL?
- How do you get a portion of a string?
- What’s the difference between CHAR_LENGTH and LENGTH?
- How do you convert a string to UTF-8?
- What do % and _ mean inside LIKE statement?
- What does + mean in REGEXP?
- How do you get the month from a timestamp?
- How do you offload the time/date handling to MySQL?
- How do you add three minutes to a date?
- What’s the difference between Unix timestamps and MySQL timestamps?
- How do you convert between Unix timestamps and MySQL timestamps?
- What are ENUMs used for in MySQL?
- How are ENUMs and SETs represented internally?
- How do you start and stop MySQL on Windows?
- How do you start MySQL on Linux?
- Explain the difference between mysql and mysqli interfaces in PHP?
- What’s the default port for MySQL Server?
- What does tee command do in MySQL?
- Can you save your connection settings to a conf file?
- How do you change a password for an existing user via mysqladmin?
- Use mysqldump to create a copy of the database?
- Have you ever used MySQL Administrator and MySQL Query Browser?
- What are some good ideas regarding user security in MySQL?
- Explain the difference between MyISAM Static and MyISAM Dynamic.
- What does myisamchk do?
- Explain advantages of InnoDB over MyISAM?
- Explain advantages of MyISAM over InnoDB?
- What are HEAP tables in MySQL?
- How do you control the max size of a HEAP table?
- What are CSV tables?
- Explain federated tables.
- What is SERIAL data type in MySQL?
- What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
- Explain the difference between BOOL, TINYINT and BIT.
- Explain the difference between FLOAT, DOUBLE and REAL.
- If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
- What happens if a table has one column defined as TIMESTAMP?
- But what if you really want to store the timestamp data, such as the publication date of the article?
- Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
- Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44? ON UPDATE CURRENT_TIMESTAMP.
- If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?
- General Information About MySQL
- The Main Features of MySQL
- Database Basics
- MySQL Command Interpreter
- Installing a MySQL Binary Distribution
- MySQL - Quick Installation Overview
- MySQL - MySQL Extensions to ANSI SQL92
- MySQL - Running MySQL in ANSI Mode
- Functionality Missing from MySQL - Sub-selects
- Functionality Missing from MySQL - SELECT INTO TABLE
- Functionality Missing from MySQL - Transactions
- Functionality Missing from MySQL - Stored Procedures and Triggers
- Functionality Missing from MySQL - Foreign Keys
- MySQL - Reasons NOT to Use Foreign Keys constraints
- Functionality Missing from MySQL - `--' as the Start of a Comment
- Functionality Missing from MySQL - How to Cope Without COMMIT/ROLLBACK
- MySQL - General Security
- How to Make MySQL Secure Against Crackers
- MySQL - Startup options to mysqld which concerns security
- MySQL - What the Privilege System Does
- MySQL - User Names and Passwords
- How to connecting to the MySQL Server
- MySQL - Keeping Your Password Secure
- Privileges Provided by MySQL
- MySQL - How the Privilege System Works
- MySQL - Access Control, Stage 1: Connection Verification
- MySQL - Access Control
- MySQL - When Privilege Changes Take Effect
- Setting Up the Initial MySQL Privileges
- Adding New User Privileges to MySQL
- MySQL - Setting Up Passwords
- MySQL - Causes of Access denied Errors
- Replication in MySQL
- MySQL - Replication Implementation Overview
- MySQL - how to set up complete replication on your current MySQL server
- MySQL - Replication Features and known problems
- MySQL - SQL Commands Related to Replication
- MySQL - Why do I sometimes see more than one Binlog_Dump thread on the master after I have restarted the slave?
- MySQL - How do I rotate replication logs?
- MySQL - How do I upgrade on a hot replication setup?
- MySQL - What issues should I be aware of when setting up two-way replication?
- MySQL - How can I use replication to improve performance of my system?
- MySQL - What should I do to prepare my client code to use performance-enhancing replication?
- MySQL - When and how much can MySQL replication improve the performance of my system?
- MySQL - How can I use replication to provide redundancy/high availability?
- MySQL - Troubleshooting Replication
- How to get Maximum Performance from MySQL
- MySQL - Optimization Overview
- MySQL - System/Compile Time and Startup Parameter Tuning
- MySQL - Disk Issues
- MySQL - Using Symbolic Links for Databases and Tables
- MySQL - Tuning Server Parameters
- How MySQL Opens and Closes Tables
- MySQL - Drawbacks to Creating Large Numbers of Tables in the Same Database
- MySQL - Why So Many Open tables?
- How MySQL Uses Memory
- How MySQL Locks Tables
- MySQL - Table Locking Issues
- How MySQL uses DNS
- MySQL - Get Your Data as Small as Possible
- How MySQL Uses Indexes
- MySQL - Speed of Queries that Access or Update Data
- MySQL - Estimating Query Performance
- MySQL - Speed of SELECT Queries
- How MySQL Optimizes WHERE Clauses
- How MySQL Optimizes DISTINCT
- How MySQL Optimizes LEFT JOIN and RIGHT JOIN
- How MySQL Optimizes LIMIT
- MySQL - Speed of INSERT Queries
- MySQL - Speed of UPDATE Queries
- MySQL - Speed of DELETE Queries
- MySQL - Other Optimization Tips
- MySQL - Using Your Own Benchmarks
- How MySQL Stores Its Row Data and Index Data?
- MySQL is Portability
- What Have We Used MySQL For?
- What is the difference between mysql_fetch_array and mysql_fetch_object?
- What are the different table present in MYsql?
| 