MYSQL FOREIGN KEY DOCUMENTATION

FOREIGN KEY Constraints

InnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name, ...)
   REFERENCES tbl_name (index_col_name,...)
   [ON DELETE reference_option]
   [ON UPDATE reference_option]

reference_option:
   RESTRICT | CASCADE | SET NULL | NO ACTION

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name.

Foreign keys definitions are subject to the following conditions:

• Both tables must be InnoDB tables and they must not be TEMPORARY tables.

• Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.

• InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

• InnoDB allows a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

• Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

• If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

InnoDB rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. The action InnoDB takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, InnoDB supports five options regarding the action to be taken. If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT.

• CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

  Note

  Currently, triggers are not activated by cascaded foreign key actions.

• SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

  If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

• NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not allowed to proceed if there is a related foreign key value in the referenced table. InnoDB rejects the delete or update operation for the parent table.

• RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. (Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.)

• SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

Here is a simple example that relates parent and child tables through a single-column foreign key:

CREATE TABLE parent (id INT NOT NULL,
                    PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) ENGINE=INNODB;

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                     price DECIMAL,
                     PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                           product_category INT NOT NULL,
                           product_id INT NOT NULL,
                           customer_id INT NOT NULL,
                           PRIMARY KEY(no),
                           INDEX (product_category, product_id),
                           FOREIGN KEY (product_category, product_id)
                             REFERENCES product(category, id)
                             ON UPDATE CASCADE ON DELETE RESTRICT,
                           INDEX (customer_id),
                           FOREIGN KEY (customer_id)
                             REFERENCES customer(id)) ENGINE=INNODB;

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
   ADD [CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name, ...)
   REFERENCES tbl_name (index_col_name,...)
   [ON DELETE reference_option]
   [ON UPDATE reference_option]

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

InnoDB supports the use of ALTER TABLE to drop foreign keys:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the FOREIGN KEY clause included a CONSTRAINT name when you created the foreign key, you can refer to that name to drop the foreign key. Otherwise, the fk_symbol value is internally generated by InnoDB when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use the SHOW CREATE TABLE statement. For example:

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
      Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
 `A` int(11) NOT NULL auto_increment,
 `D` int(11) NOT NULL default '0',
 `B` varchar(200) NOT NULL default '',
 `C` varchar(175) default NULL,
 PRIMARY KEY  (`A`,`D`,`B`),
 KEY `B` (`B`,`C`),
 KEY `C` (`C`),
 CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`; 

You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. Separate statements are required.

If ALTER TABLE for an InnoDB table results in changes to column values (for example, because a column is truncated), InnoDB's FOREIGN KEY constraint checks do not notice possible violations caused by changing the values.

The InnoDB parser allows table and column identifiers in a FOREIGN KEY ... REFERENCES ... clause to be quoted within backticks. (Alternatively, double quotes can be used if the ANSI_QUOTES SQL mode is enabled.) The InnoDB parser also takes into account the setting of the lower_case_table_names system variable.

InnoDB returns a table's foreign key definitions as part of the output of the SHOW CREATE TABLE statement:

SHOW CREATE TABLE tbl_name;

mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.

You can also display the foreign key constraints for a table like this:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

The foreign key constraints are listed in the Comment column of the output.

When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit.

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1; 

This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. Setting foreign_key_checks to 0 can also be useful for ignoring foreign key constraints during LOAD DATA and ALTER TABLE operations. However, even if foreign_key_checks = 0, InnoDB does not allow the creation of a foreign key constraint where a column references a non-matching column type. Also, if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first.

InnoDB does not allow you to drop a table that is referenced by a FOREIGN KEY constraint, unless you do SET foreign_key_checks = 0. When you drop a table, the constraints that were defined in its create statement are also dropped.

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Important

For users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including InnoDB, recognizes or enforces the MATCH clause used in referential integrity constraint definitions. Use of an explicit MATCH clause will not have the specified effect, and also causes ON DELETE and ON UPDATE clauses to be ignored. For these reasons, specifying MATCH should be avoided.

The MATCH clause in the SQL standard controls how NULL values in a composite (multiple-column) foreign key are handled when comparing to a primary key. InnoDB essentially implements the semantics defined by MATCH SIMPLE, which allow a foreign key to be all or partially NULL. In that case, the (child table) row containing such a foreign key is allowed to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers.

Additionally, MySQL and InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to non-unique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys.

Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

Deviation from SQL standards: If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not allow the deletion of any of those parent rows.

InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

Deviation from SQL standards: A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL.

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be

Mysql limit packet

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, start mysql like this:

mysql> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

mysql> mysqld --max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

mysql> mysqld --set-variable=max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = max_allowed_packet=16M

It's safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you don't run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.

MYSQL CONFIGURATIONS

Configure MySQL Options

Related Documentation

MySQL Documentation

Prerequisites

Last Updated for InterWorx version 3.0.1

No special prerequisites.

If you are running a version of MySQL previous to 4.1 on your server, the following system variables will not be available for modification through the NodeWorx MySQL interface: Query Cache Limt and Query Cache Size. See the variable descriptions below for more details.

Purpose

The following procedures explain how to configure MySQL options using InterWorx-CP. The most common configuration options are exposed in the InterWorx-CP. As with many of the system services, a system administrator still retains the ability to configure the service by editing the configuration file.

Procedure - Change a Commonly Configured MySQL Option

1. Click on the System Services menu if it is not already expanded.
2. Click on the ♦ MySQL Server submenu if it is not already expanded.
3. Click on the ◊ Overview item.
4. You should now be looking at the MySQL Server Management controls in the main content area.
5. Locate the MySQL Server Options section.
6. Change the option(s) you wish to update to the desired value(s).
7. Click the Update button.
8. You will see the following message at the top of the screen: » Directives updated successfully.

MySQL Server Options

Connections

The maximum number of simultaneous connections allowed for MySQL. The number of connections must be between 1 and 65536, with the default value set to 100. Increasing max_connections increases the number of file descriptors that mysqld requires. It is strongly suggested that you keep the maximum number of connections below 1500.

MySQL Dynamic System Variables
MySQL System Variables Overview
Receiving a Too Many Connections Error?

Connection Errors

The maximum number of allowed connection errors for a given host. The value must be between 1 and 999999999. If set, the server blocks further connections from a remote host when the number of interrupted connections from that host exceeds this number. A successful connection from a host resets the number of connection errors. You can also unblock a host with the MySQL command FLUSH HOSTS.

FLUSH Command Syntax

Connect Timeout

The maximum number of seconds before a connection to the MySQL server will time out. Possible values range from 2 to 999999999 seconds.

MySQL Dynamic System Variables

Wait Timeout

The number of seconds the server waits for activity on a connection before closing the connection. Values can be between 2 and 999999999. On thread startup, SESSION.WAIT_TIMEOUT is initialized from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option)

MySQL Server System Variables

Key Buffer Size

The key buffer is a memory buffer shared by all threads. The key buffer size mut be at least 16384 bytes. Increasing the key buffer size will result in better index handling (for all reads and multiple writes). 64M on a 256M machine that mainly runs MySQL is quite common. If you, however, make this too big (for instance more than 50% of your total memory) your system may start to page and become extremely slow. Remember that because MySQL does not cache data reads, you will have to leave some room for the OS filesystem cache.

MySQL Server System Variables

Sort Buffer Size

The size of the buffer used when sorting table data. The minimum value is 8192 bytes and the default value is 1MB. Increasing this value will lead to faster ORDER BY or GROUP BY operations. A temporary buffer of sort_buffer_size is allocated when a thread needs to do perform a sort operation.

Information About MySQL Temporary Files

Read Buffer Size

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value. The minimum value for read_buffer_size is 8192 bytes, and the default value is 128KB. In versions of MySQL prior to 4.x, this system variable was referred to as record_buffer.

MySQL Server System Variables

Maximum Allowed Packet Size

The maximum size of one packet. The minimum value for this variable is 1024 Bytes. By default, the value is small so as to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns, as it should be as big as the biggest BLOB you want to use. The protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0.

Packet Too Large?

Thread Cache Size

The number of threads that are kept in the cache for reuse. This variable can be increased to improve performance if you have a lot of new connections. Increasing this value increases the number of file descriptors that mysqld requires.

MySQL Server System Variables

Table Cache

This variable controls the number of open tables that are cached. Table cache is related to max connections. For example, for 200 concurrent running connections, you should have a table cache of at leat 200 * N, where N is the maximum number of tables in a join. If you increase this value, the number of file descriptors needed by MySQLD will also increase. You can check if you need to increase the table cache by checking the Opened_tables variable. If this variable is big and you don’t do FLUSH TABLES a lot (which just forces all tables to be closed and reopenend), then you should increase the value of this variable.

How MySQL Opens and Closes Tables
FLUSH Command Syntax

Query Cache Limit

Sets the maximum amount of memory to be allocated for storage of old query results. The default value is 1MB. This variable is only available in versions of MySQL > 4.1.x.

Query Cache Size

Sets size of the query cache. The default value is 0. If this is set to 0, query caching is disabled. This variable is only available in versions of MySQL > 4.1.x.

DIFFERENT BETWEEN InnoDB AND MyISAM

• The big difference between MySQL Table Type MyISAM and InnoDB is that InnoDB supports transaction
• InnoDB supports some newer features: Transactions, row-level locking, foreign keys
• InnoDB is for high volume, high performance

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine.With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence.

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Fully integrated with MySQL Server, the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be of any size even on operating systems where file size is limited to 2GB.

So, what do you think about those engines?? Please feel free to discuss it.

For more information visit http://dev.mysql.com/

Thanks

MyISAM is of course the default table type i'm MySQL. It's an improvement over the now-deprecated ISAM table type. It provides a simple tree structure for quick indexing on small tables, typicall fewer than 10,000 rows of data.

There are some other types of tables supported in MySQL, most of which won't provide any noticable differences.

A MERGE table is a table that only exists in memory, and will not be saved if MySQL crashes or has to be restarted. It is, as the name suggests, a merge of two (or more) tables. If you drop a MERGE table, it only drops the reference to the merge, not to the tables that are combined in it. It's not good for much!

Another table, the one which is best for huge tables, is InnoDB. It's probably the best RDBMS out there; it's actually a whole set of drivers on it's own, and when you use it, MySQL is really just a wrapper around the InnoDB table. Slashdot and other megamammoth sites out there use it, it's fast and effecient, but the trees are pretty large, so it's not good for smaller tables.

There are a few others.. like BerkeleyDB, which isn't anything special and doesn't provide any major performance increase.

The major difference between the various table types are the data tree structures and how they're handled within the RDBMS.

In the end, you'll probably find yourself sticking to MyISAM unless you're on a very large-scale project, in which cas eyou probably woudln't use MySQL anyway

LEAN TO MAKE BETTER DECISIONS.

A PRUDENT MAN GIVES THOUGHT TO HIS STEPS.

If you're still making the same mistakes at fifty that you were at twenty, you need to ask God for wisdom. “The heart of the discerning acquires knowledge; the ears of the wise seek it out”. What you are in the present, was determined by the decisions you made in the past. If you want to change your future, learn to make better decisions. Be wise; let these principles guide you:-

1. Never make permanent decision based on temporary circumstances. If you do, you'll regret it.

2. Know that you can do things in the same way and get different results, you need to be dynamic in doing things anytime.

3. Don't let your emotions blind you to reason. Pray, weigh things carefully and base your decision on mature judgment.

4. Surround yourself with sharp people and draw on their gifts, without be intimidated by their expertise.

5. Take the time to consider all options. What look good to you today, may not look so good tomorrow.

6. You can't fight successfully on every front, so choose your battles carefully. Simply stated; some things are not worth fighting for.

7. Take time to get all the facts; conjecture leads to crisis.

8. Consider the consequences of each action. Ask yourself, 'Am I ready to handle this right now?'.

9. Make sure your expectations don't exceed your potential and your resources. Be realistic. If you can't count, don't apply for a job in the finance office. If you can't sing, don't cut am album. Focus on what God gifted you to do. That is where you'll succeed.

10. Time is your most limited and valuable resource. Don't waste it.

11. Allow yourself a 10% risk of being wrong, A 50% likelihood of betrayal, and A 100% commitment to trust God, go forward and survive it all.