•  Print this page
    Print this page

MySQL
 
 
MySQL is a fast, multi-threaded, multi-user open source SQL database server.

The MySQL database is released under the GNU General Public License (GPL). MySQL can be used for free when you are not distributing any part of the MySQL system. For example: If you run a commercial web site using the MySQL Database Software as long as MySQL is not distributed.

Please read the MySQL manual for situations where a commercial license is needed.

More information about MySQL can be found at: https://www.mysql.com

MySQL online manual can be found at: https://dev.mysql.com/doc/index.html

The latest MySQL version can be downloaded from: https://dev.mysql.com/downloads/index.html

Quick guides







Foreign keys.



Information
MySQL ONLY supports foreign keys when you use InnoDB tables.
Note: If you set FOREIGN KEY (..) you must also set INDEX(..). See examples below.

Foreign key constraints:

Constraint Description
ON DELETE CASCADE When a row in the parent table is deleted, InnoDB will automatically delete corresponding foreign key column in all matching rows in the child table.
ON DELETE SET NULL When a row in the parent table is deleted, InnoDB will automatically set corresponding foreign key column in all matching rows in the child table to NULL.
ON DELETE RESTRICT ON DELETE RESTRICT disallows a delete if an associated record still exists.
ON UPDATE CASCADE When a row in the parent table is updated, InnoDB will automatically update corresponding foreign key column in all matching rows in the child table to the same value.
ON UPDATE SET NULL When a row in the parent table is updated, InnoDB will automatically set corresponding foreign key column in all matching rows in the child table to NULL.
ON UPDATE RESTRICT ON UPDATE RESTRICT disallows an update if an associated record still exists.


Operating system used
Windows XP Home Edition Version 5.1 SP 2

Software prerequisites
MySQL 3.23 or higher

Examples

CREATE TABLE country (
    countryid int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    countrycode varchar(2) NOT NULL DEFAULT '',
    countryname varchar(80) NOT NULL DEFAULT '',
    UNIQUE (countryname)
) TYPE = INNODB;



CREATE TABLE user (
    userid int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(20) NOT NULL DEFAULT '',
    password char(32) binary NOT NULL DEFAULT '',
    firstname varchar(50) NOT NULL DEFAULT '',
    lastname varchar(50) NOT NULL DEFAULT '',
    countryid smallint NOT NULL,
    UNIQUE (username),
    INDEX(countryid),
    FOREIGN KEY (countryid) REFERENCES country(countryid) ON DELETE RESTRICT
    ON UPDATE CASCADE
) TYPE = INNODB;

CREATE TABLE books_bought (
    userid int NOT NULL,
    bookid int NOT NULL,
    INDEX(userid),
    FOREIGN KEY (userid) REFERENCES user(userid) ON DELETE CASCADE
    ON UPDATE CASCADE,
    INDEX(bookid),
    FOREIGN KEY (bookid) REFERENCES book(bookid) ON DELETE CASCADE
    ON UPDATE CASCADE
) TYPE = INNODB;