CREATE TABLE
Syntax
1043
Examples of Foreign Key Clauses
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),
INDEX (customer_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;
Adding foreign keys
You can add a new foreign key constraint to an existing table by using
ALTER TABLE
. The syntax
relating to foreign keys for this statement is shown here:
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.
Dropping Foreign Keys
You can also use
ALTER TABLE
to drop foreign keys, using the syntax shown here:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
