Entry
Database: MySQL: Table:Master:Detail:How create master detail table in MySQL? [foreign key / InnoDB]
May 22nd, 2005 00:25
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 10 March 2005 - 08:32 pm ----------------------
Database: MySQL: Table:Master:Detail:How create master detail table in
MySQL? [foreign key / InnoDB]
---
You will need MySql version 3.23.29 or higher to be able to
implement this.
===
You basically have to add the following in MySql
1. to the master table, you have to add the INNODB type
--- cut here: begin --------------------------------------------------
CREATE TABLE tableYourMasterName (
...
)
TYPE = INNODB;
--- cut here: end ----------------------------------------------------
---
2. You have to add the following lines to each of your detail
tables in MySql
---
--- cut here: begin --------------------------------------------------
CREATE TABLE tableYourDetailName (
...
INDEX ( columnYourForeignKey ),
FOREIGN KEY ( columnYourForeignKey ) REFERENCES tableYourMasterName (
columnYourMasterPrimaryKey )
)
TYPE = INNODB;
--- cut here: end ----------------------------------------------------
---
You supply thus so the minimal information, that is:
1. on which column to create an index
(that should here be your foreign key column)
2. the name of your foreign key
3. the name of your master table
4. the name of the primary key of that master table
it refers to
5. you have to use the INNODB table type
(by design)
===
Note:
For foreign keys, your table should be of type
InnoDB
(thus not e.g. MYISAM)
or it might/will not work as designed.
===
e.g.
Given 2 tables where in table1 the primary key 'columnNumber' is linked
in table2 to the foreign key 'columnNumber'.
table: tableName
-----------------------------------------------------------------
| columnNumber | columnName |
-----------------------------------------------------------------
1 John
2 Vanessa
3 Bella
-----------------------------------------------------------------
table: tableStudy
----------------------------------------
| columStudy | columnNumber |
----------------------------------------
Physics 1
Mathematics 2
Computer science 3
----------------------------------------
---
Steps: Overview:
1. -Start MySql (version 3.23.29 or higher)
1. Open an MSDOS command prompt
2. Type the command to start the MySql interpreter:
e.g.
"c:\program files\MySql\MySql Server 4.1\bin\mysql.exe"
2. -Create a database
1. Type the command
CREATE DATABASE
databaseMyTest1;
3. -Open this database
1. Type the command
USE databaseMyTest1;
4. -Both tables need to be of type 'INNODB'
5. -Create the first table :
1. Type or paste the command
--- cut here: begin --------------------------------------------------
CREATE TABLE
tableName
(
columnNumber INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnName VARCHAR( 50 ),
)
TYPE = INNODB;
--- cut here: end ----------------------------------------------------
6. -Create the second table:
1. Type or paste the command
--- cut here: begin --------------------------------------------------
CREATE TABLE
tableStudy
(
columnStudy VARCHAR( 50 ),
columnNumber INT NOT NULL,
INDEX ( columnNumber ),
FOREIGN KEY ( columnNumber ) REFERENCES tableName ( columnNumber )
)
TYPE = INNODB;
--- cut here: end ----------------------------------------------------
7. -Now insert some data in the first table
1. Type or paste the command
--- cut here: begin --------------------------------------------------
INSERT INTO
tableName
(
columnName
)
VALUES
(
'John'
)
;
INSERT INTO
tableName
(
columnName
)
VALUES
(
'Vanessa'
)
;
INSERT INTO
tableName
(
columnName
)
VALUES
(
'Bella'
)
;
--- cut here: end ----------------------------------------------------
2. -Show the result
1. -Type the command
--- cut here: begin --------------------------------------------------
SELECT * FROM TableName;
--- cut here: end ----------------------------------------------------
2. That will show
+--------------+------------+
| columnNumber | columnName |
+--------------+------------+
| 1 | John |
| 2 | Vanessa |
| 3 | Bella |
+--------------+------------+
8. -Now insert some data in the second table
1. Type or paste the command
--- cut here: begin --------------------------------------------------
INSERT INTO
tableStudy
(
columnStudy,
columnNumber
)
VALUES
(
'Physics',
'1'
)
;
INSERT INTO
tableStudy
(
columnStudy,
columnNumber
)
VALUES
(
'Mathematics',
'2'
)
;
INSERT INTO
tableStudy
(
columnStudy,
columnNumber
)
VALUES
(
'Computer science',
'3'
)
;
--- cut here: end ----------------------------------------------------
9. -Show the result
1. -Type the command
--- cut here: begin --------------------------------------------------
SELECT
*
FROM
tableStudy
;
+------------------+--------------+
| columnStudy | columnNumber |
+------------------+--------------+
| Physics | 1 |
| Mathematics | 2 |
| Computer science | 3 |
+------------------+--------------+
--- cut here: end ----------------------------------------------------
10. -Show the inner join result
1. -Type the command
--- cut here: begin --------------------------------------------------
SELECT
columnName,
columnStudy
FROM
tableName,
tableStudy
WHERE
tableName.columnNumber = tableStudy.columnNumber
;
+------------+------------------+
| columnName | columnStudy |
+------------+------------------+
| John | Physics |
| Vanessa | Mathematics |
| Bella | Computer science |
+------------+------------------+
--- cut here: end ----------------------------------------------------
---
---
Or alltogether in a script
(run this script, by starting 'mysql.exe' in the bin directory
in your MySql directory, then type the command
'source <your script filename>'
e.g. source test.sql
)
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE DATABASE
databaseMyTest1;
-----------------------------------------------
USE databaseMyTest1;
-----------------------------------------------
DROP TABLE
tableStudy;
-----------------------------------------------
DROP TABLE
tableName;
-----------------------------------------------
CREATE TABLE
tableName
(
columnNumber INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnName VARCHAR( 50 ),
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
tableStudy
(
columnStudy VARCHAR( 50 ),
columnNumber INT NOT NULL,
INDEX ( columnNumber ),
FOREIGN KEY ( columnNumber ) REFERENCES tableName ( columnNumber )
)
TYPE = INNODB;
-----------------------------------------------
INSERT INTO
tableName
(
columnName
)
VALUES
(
'John'
)
;
-----------------------------------------------
INSERT INTO
tableName
(
columnName
)
VALUES
(
'Vanessa'
)
;
-----------------------------------------------
INSERT INTO
tableName
(
columnName
)
VALUES
(
'Bella'
)
;
-----------------------------------------------
SELECT
*
FROM
tableName;
-----------------------------------------------
INSERT INTO
tableStudy
(
columnStudy,
columnNumber
)
VALUES
(
'Physics',
'1'
)
;
-----------------------------------------------
INSERT INTO
tableStudy
(
columnStudy,
columnNumber
)
VALUES
(
'Mathematics',
'2'
)
;
-----------------------------------------------
INSERT INTO
tableStudy
(
columnStudy,
columnNumber
)
VALUES
(
'Computer science',
'3'
)
;
-----------------------------------------------
SELECT
*
FROM
tableStudy
;
-----------------------------------------------
SELECT
columnName,
columnStudy
FROM
tableName,
tableStudy
WHERE
tableName.columnNumber = tableStudy.columnNumber
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
when you run this, this will show:
--- cut here: begin --------------------------------------------------
mysql> source c:\temp\test.sql
ERROR 1007 (HY000): Can't create database 'databasemytest1'; database
exists
Database changed
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected, 1 warning (0.17 sec)
Query OK, 0 rows affected, 1 warning (0.13 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
+--------------+------------+
| columnNumber | columnName |
+--------------+------------+
| 1 | John |
| 2 | Vanessa |
| 3 | Bella |
+--------------+------------+
3 rows in set (0.00 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
+------------------+--------------+
| columnStudy | columnNumber |
+------------------+--------------+
| Physics | 1 |
| Mathematics | 2 |
| Computer science | 3 |
+------------------+--------------+
3 rows in set (0.01 sec)
+------------+------------------+
| columnName | columnStudy |
+------------+------------------+
| John | Physics |
| Vanessa | Mathematics |
| Bella | Computer science |
+------------+------------------+
3 rows in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
11. -Now try to fill a foreign key to a non-existing primary key
--- cut here: begin --------------------------------------------------
INSERT INTO
tableStudy
(
ColumnStudy,
ColumnNumber
)
VALUES
(
'Chemistry',
'4'
)
;
--- cut here: end ----------------------------------------------------
1. If you try this, you will get the error
--- cut here: begin --------------------------------------------------
'ERROR 1216 (23000):
Cannot add or update a child row:
a foreign key constraint fails'
--- cut here: end ----------------------------------------------------
12. -Now try to delete a primary key
--- cut here: begin --------------------------------------------------
DELETE FROM
tableName
WHERE
columnnumber = "1"
;
--- cut here: end ----------------------------------------------------
1. If you try this, you will get the error
'ERROR 1217 (23000):
Cannot delete or update a parent row:
a foreign key constraint fails'
---
---
13. -Some more examples:
e.g.
table: tableCustomer
----------------------------------------
| columnCustomerId | columnName |
----------------------------------------
1 John
2 Vanessa
3 Bella
----------------------------------------
table: tableCustomerSales
----------------------------------------------------------------
| columnTransactionId | columnAmount | columnCustomerId |
----------------------------------------------------------------
1 1000 1
2 2000 2
3 3000 3
----------------------------------------------------------------
--- cut here: begin --------------------------------------------------
CREATE TABLE
tableCustomer
(
columnCustomerId INT NOT NULL,
columnName VARCHAR( 50 ),
PRIMARY KEY ( columnCustomerId )
)
TYPE = INNODB;
CREATE TABLE
tableCustomerSales
(
columnTransactionId INT NOT NULL,
columnAmount INT,
columnCustomerId INT NOT NULL,
PRIMARY KEY( columnTransactionId ),
INDEX index2 ( columnCustomerId ),
FOREIGN KEY ( columnCustomerId ) REFERENCES tableCustomer (
columnCustomerId )
)
TYPE = INNODB;
--- cut here: end ----------------------------------------------------
---
---
--- cut here: begin --------------------------------------------------
in general:
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKey1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table2
(
columnPrimaryKey2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey1 INT,
INDEX index2 ( columnForeignKey1 ),
FOREIGN KEY ( columnForeignKey1 ) REFERENCES <your tablename
containing the primary key> ( columnPrimaryKey1 )
ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
---
---
e.g.
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKey1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table2
(
columnPrimaryKey2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey21 INT,
INDEX index21 ( columnForeignKey21 ),
FOREIGN KEY ( columnForeignKey21 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
---
---
e.g.
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKey1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table2
(
columnPrimaryKey2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey21 INT,
INDEX index21 ( columnForeignKey21 ),
FOREIGN KEY ( columnForeignKey21 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table3
(
columnPrimaryKey3 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey31 INT,
columnForeignKey32 INT,
INDEX index31 ( columnForeignKey31 ),
INDEX index32 ( columnForeignKey32 ),
FOREIGN KEY ( columnForeignKey31 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey32 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
---
---
e.g.
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKey1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table2
(
columnPrimaryKey2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey21 INT,
INDEX index21 ( columnForeignKey21 ),
FOREIGN KEY ( columnForeignKey21 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table3
(
columnPrimaryKey3 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey31 INT,
columnForeignKey32 INT,
INDEX index31 ( columnForeignKey31 ),
INDEX index32 ( columnForeignKey32 ),
FOREIGN KEY ( columnForeignKey31 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey32 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table4
(
columnPrimaryKey4 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey41 INT,
columnForeignKey42 INT,
columnForeignKey43 INT,
INDEX index41 ( columnForeignKey41 ),
INDEX index42 ( columnForeignKey42 ),
INDEX index43 ( columnForeignKey43 ),
FOREIGN KEY ( columnForeignKey41 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey42 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey43 ) REFERENCES table3 (
columnPrimaryKey3 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
---
---
e.g.
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKey1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table2
(
columnPrimaryKey2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey21 INT,
INDEX index21 ( columnForeignKey21 ),
FOREIGN KEY ( columnForeignKey21 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table3
(
columnPrimaryKey3 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey31 INT,
columnForeignKey32 INT,
INDEX index31 ( columnForeignKey31 ),
INDEX index32 ( columnForeignKey32 ),
FOREIGN KEY ( columnForeignKey31 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey32 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table4
(
columnPrimaryKey4 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey41 INT,
columnForeignKey42 INT,
columnForeignKey43 INT,
INDEX index41 ( columnForeignKey41 ),
INDEX index42 ( columnForeignKey42 ),
INDEX index43 ( columnForeignKey43 ),
FOREIGN KEY ( columnForeignKey41 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey42 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey43 ) REFERENCES table3 (
columnPrimaryKey3 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table5
(
columnPrimaryKey5 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnForeignKey51 INT,
columnForeignKey52 INT,
columnForeignKey53 INT,
columnForeignKey54 INT,
INDEX index51 ( columnForeignKey51 ),
INDEX index52 ( columnForeignKey52 ),
INDEX index53 ( columnForeignKey53 ),
INDEX index54 ( columnForeignKey54 ),
FOREIGN KEY ( columnForeignKey51 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey52 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey53 ) REFERENCES table3 (
columnPrimaryKey3 ) ON DELETE CASCADE,
FOREIGN KEY ( columnForeignKey54 ) REFERENCES table4 (
columnPrimaryKey4 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
---
---
Or alltogether in a script
(run this script, by starting 'mysql.exe' in the bin directory
in your MySql directory, then type the command
'source <your script filename>'
e.g. source test.sql
)
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE DATABASE
database1;
-----------------------------------------------
USE
database1;
-----------------------------------------------
DROP TABLE
table5;
-----------------------------------------------
DROP TABLE
table4;
-----------------------------------------------
DROP TABLE
table3;
-----------------------------------------------
DROP TABLE
table2;
-----------------------------------------------
DROP TABLE
table1;
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKey1 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnS1 VARCHAR( 50 )
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table2
(
columnPrimaryKey2 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnS2 VARCHAR( 50 ),
columnForeignKey21 INT,
INDEX index21 ( columnForeignKey21 ),
FOREIGN KEY ( columnForeignKey21 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table3
(
columnPrimaryKey3 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnS3 VARCHAR( 50 ),
columnForeignKey31 INT,
INDEX index31 ( columnForeignKey31 ),
FOREIGN KEY ( columnForeignKey31 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
columnForeignKey32 INT,
INDEX index32 ( columnForeignKey32 ),
FOREIGN KEY ( columnForeignKey32 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table4
(
columnPrimaryKey4 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnS4 VARCHAR( 50 ),
columnForeignKey41 INT,
INDEX index41 ( columnForeignKey41 ),
FOREIGN KEY ( columnForeignKey41 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
columnForeignKey42 INT,
INDEX index42 ( columnForeignKey42 ),
FOREIGN KEY ( columnForeignKey42 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE,
columnForeignKey43 INT,
INDEX index43 ( columnForeignKey43 ),
FOREIGN KEY ( columnForeignKey43 ) REFERENCES table3 (
columnPrimaryKey3 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
CREATE TABLE
table5
(
columnPrimaryKey5 INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
columnS5 VARCHAR( 50 ),
columnForeignKey51 INT,
INDEX index51 ( columnForeignKey51 ),
FOREIGN KEY ( columnForeignKey51 ) REFERENCES table1 (
columnPrimaryKey1 ) ON DELETE CASCADE,
columnForeignKey52 INT,
INDEX index52 ( columnForeignKey52 ),
FOREIGN KEY ( columnForeignKey52 ) REFERENCES table2 (
columnPrimaryKey2 ) ON DELETE CASCADE,
columnForeignKey53 INT,
INDEX index53 ( columnForeignKey53 ),
FOREIGN KEY ( columnForeignKey53 ) REFERENCES table3 (
columnPrimaryKey3 ) ON DELETE CASCADE,
columnForeignKey54 INT,
INDEX index54 ( columnForeignKey54 ),
FOREIGN KEY ( columnForeignKey54 ) REFERENCES table4 (
columnPrimaryKey4 ) ON DELETE CASCADE
)
TYPE = INNODB;
-----------------------------------------------
INSERT INTO
table1
(
columnS1
)
VALUES
(
"John"
)
;
-----------------------------------------------
SELECT
*
FROM
table1
;
-----------------------------------------------
SELECT
columnS1
FROM
table1
;
-----------------------------------------------
INSERT INTO
table2
(
columnS2,
columnForeignKey21
)
VALUES
(
"Doe",
1
)
;
-----------------------------------------------
SELECT
*
FROM
table2
;
-----------------------------------------------
SELECT
*
FROM
table1,
table2
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
;
-----------------------------------------------
SELECT
columnS1,
columnS2
FROM
table1,
table2
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
;
-----------------------------------------------
SELECT
columnS1,
columnS2
FROM
table1,
table2
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
;
-----------------------------------------------
INSERT INTO
table3
(
columnS3,
columnForeignKey31,
columnForeignKey32
)
VALUES
(
"High Street 987",
1,
1
)
;
-----------------------------------------------
SELECT
*
FROM
table3
;
-----------------------------------------------
SELECT
*
FROM
table1,
table2,
table3
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
AND
table2.columnForeignKey21 = table3.columnForeignKey31
;
-----------------------------------------------
SELECT
columnS1,
columnS2,
columnS3
FROM
table1,
table2,
table3
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
AND
table2.columnForeignKey21 = table3.columnForeignKey31
;
-----------------------------------------------
INSERT INTO
table4
(
columnS4,
columnForeignKey41,
columnForeignKey42,
columnForeignKey43
)
VALUES
(
"New York",
1,
1,
1
)
;
-----------------------------------------------
SELECT
*
FROM
table4
;
-----------------------------------------------
SELECT
*
FROM
table1,
table2,
table3,
table4
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
AND
table2.columnForeignKey21 = table3.columnForeignKey31
AND
table3.columnForeignKey31 = table4.columnForeignKey41
;
-----------------------------------------------
SELECT
columnS1,
columnS2,
columnS3,
columnS4
FROM
table1,
table2,
table3,
table4
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
AND
table2.columnForeignKey21 = table3.columnForeignKey31
AND
table3.columnForeignKey31 = table4.columnForeignKey41
;
-----------------------------------------------
INSERT INTO
table5
(
columnS5,
columnForeignKey51,
columnForeignKey52,
columnForeignKey53,
columnForeignKey54
)
VALUES
(
"USA",
1,
1,
1,
1
)
;
-----------------------------------------------
SELECT
*
FROM
table5
;
-----------------------------------------------
SELECT
*
FROM
table1,
table2,
table3,
table4,
table5
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
AND
table2.columnForeignKey21 = table3.columnForeignKey31
AND
table3.columnForeignKey31 = table4.columnForeignKey41
AND
table4.columnForeignKey41 = table5.columnForeignKey51
;
-----------------------------------------------
SELECT
columnS1,
columnS2,
columnS3,
columnS4,
columnS5
FROM
table1,
table2,
table3,
table4,
table5
WHERE
table1.columnPrimaryKey1 = table2.columnForeignKey21
AND
table2.columnForeignKey21 = table3.columnForeignKey31
AND
table3.columnForeignKey31 = table4.columnForeignKey41
AND
table4.columnForeignKey41 = table5.columnForeignKey51
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
when you run this, this will show:
--- cut here: begin --------------------------------------------------
mysql> source c:\temp\test.sql
ERROR 1007 (HY000): Can't create database 'database1'; database exists
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected, 1 warning (0.13 sec)
Query OK, 0 rows affected, 1 warning (0.11 sec)
Query OK, 0 rows affected, 1 warning (0.20 sec)
Query OK, 0 rows affected, 1 warning (0.26 sec)
Query OK, 0 rows affected, 1 warning (0.27 sec)
Query OK, 1 row affected (0.05 sec)
+-------------------+----------+
| columnPrimaryKey1 | columnS1 |
+-------------------+----------+
| 1 | John |
+-------------------+----------+
1 row in set (0.01 sec)
+----------+
| columnS1 |
+----------+
| John |
+----------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.07 sec)
+-------------------+----------+--------------------+
| columnPrimaryKey2 | columnS2 | columnForeignKey21 |
+-------------------+----------+--------------------+
| 1 | Doe | 1 |
+-------------------+----------+--------------------+
1 row in set (0.00 sec)
+-------------------+----------+-------------------+----------+--------
------------+
| columnPrimaryKey1 | columnS1 | columnPrimaryKey2 | columnS2 |
columnForeignKey21 |
+-------------------+----------+-------------------+----------+--------
------------+
| 1 | John | 1 | Doe |
1 |
+-------------------+----------+-------------------+----------+--------
------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.05 sec)
+----------+----------+
| columnS1 | columnS2 |
+----------+----------+
| John | Doe |
+----------+----------+
1 row in set (0.00 sec)
+-------------------+-----------------+--------------------+-----------
---------+
| columnPrimaryKey3 | columnS3 | columnForeignKey31 |
columnForeignKey32 |
+-------------------+-----------------+--------------------+-----------
---------+
| 1 | High Street 987 | 1
| 1 |
+-------------------+-----------------+--------------------+-----------
---------+
1 row in set (0.00 sec)
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+
| columnPrimaryKey1 | columnS1 | columnPrimaryKey2 | columnS2 |
columnForeignKey21 | columnPrimaryKey3 | columnS3 |
columnForeignKey31 | columnForeignKey32 |
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+
| 1 | John | 1 | Doe |
1 | 1 | High Street 987
| 1 | 1 |
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+
1 row in set (0.01 sec)
+----------+----------+-----------------+
| columnS1 | columnS2 | columnS3 |
+----------+----------+-----------------+
| John | Doe | High Street 987 |
+----------+----------+-----------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.03 sec)
+-------------------+----------+--------------------+------------------
--+--------------------+
| columnPrimaryKey4 | columnS4 | columnForeignKey41 |
columnForeignKey42 | columnForeignKey43 |
+-------------------+----------+--------------------+------------------
--+--------------------+
| 1 | New York | 1 |
1 | 1 |
+-------------------+----------+--------------------+------------------
--+--------------------+
1 row in set (0.00 sec)
+----------+----------+-----------------+----------+
| columnS1 | columnS2 | columnS3 | columnS4 |
+----------+----------+-----------------+----------+
| John | Doe | High Street 987 | New York |
+----------+----------+-----------------+----------+
1 row in set (0.00 sec)
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+-------------------+----------+------------------
--+--------------------+--------------------+
| columnPrimaryKey1 | columnS1 | columnPrimaryKey2 | columnS2 |
columnForeignKey21 | columnPrimaryKey3 | columnS3 |
columnForeignKey31 | columnForeignKey32 | columnPrimaryKey4 | columnS4
| columnForeignKey41 | columnForeignKey42 | columnForeignKey43 |
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+-------------------+----------+------------------
--+--------------------+--------------------+
| 1 | John | 1 | Doe
| 1 | 1 | High Street 987
| 1 | 1 | 1 | New
York | 1 | 1 | 1 |
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+-------------------+----------+------------------
--+--------------------+--------------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.03 sec)
+-------------------+----------+--------------------+------------------
--+--------------------+--------------------+
| columnPrimaryKey5 | columnS5 | columnForeignKey51 |
columnForeignKey52 | columnForeignKey53 | columnForeignKey54 |
+-------------------+----------+--------------------+------------------
--+--------------------+--------------------+
| 1 | USA | 1 |
1 | 1 | 1 |
+-------------------+----------+--------------------+------------------
--+--------------------+--------------------+
1 row in set (0.00 sec)
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+-------------------+----------+------------------
--+--------------------+--------------------+-------------------+------
----+--------------------+--------------------+--------------------+---
-----------------+
| columnPrimaryKey1 | columnS1 | columnPrimaryKey2 | columnS2 |
columnForeignKey21 | columnPrimaryKey3 | columnS3 |
columnForeignKey31 | columnForeignKey32 | columnPrimaryKey4 | columnS4
| columnForeignKey41 | columnForeignKey42 | columnForeignKey43 |
columnPrimaryKey5 | columnS5 | columnForeignKey51 | columnForeignKey52
| columnForeignKey53 | columnForeignKey54 |
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+-------------------+----------+------------------
--+--------------------+--------------------+-------------------+------
----+--------------------+--------------------+--------------------+---
-----------------+
| 1 | John | 1 | Doe
| 1 | 1 | High Street 987
| 1 | 1 | 1 | New
York | 1 | 1 | 1
| 1 | USA | 1 |
1 | 1 | 1 |
+-------------------+----------+-------------------+----------+--------
------------+-------------------+-----------------+--------------------
+--------------------+-------------------+----------+------------------
--+--------------------+--------------------+-------------------+------
----+--------------------+--------------------+--------------------+---
-----------------+
1 row in set (0.01 sec)
+----------+----------+-----------------+----------+----------+
| columnS1 | columnS2 | columnS3 | columnS4 | columnS5 |
+----------+----------+-----------------+----------+----------+
| John | Doe | High Street 987 | New York | USA |
+----------+----------+-----------------+----------+----------+
1 row in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
Tested successfully on Microsoft Windows XP Professional, running
MySql v4.1
---
---
Internet: see also:
---
Foreign keys definitions are subject to the following conditions: Both
master and detail table must be InnoDB type.
[Internet: source: http://www.google.com search for 'mysql index':
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html]
---
[Internet: see also: http://www.google.com search for 'how to
implement innodb table MySql foreign key':
http://databasejournal.com/features/mysql/article.php/2248101]
---
[Internet: see also: http://www.google.com search for 'how to create a
foreign key in MySQL?': http://groups-
beta.google.com/group/comp.databases/browse_thread/thread/70acd2f4306ec
e60/f5f8c544f4965a0f?q=how+to+create+a+foreign+key+in+MySQL%
3F#f5f8c544f4965a0f]
---
Database: Relational: MySql: Link: Overview: Can you give an overview
of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/35331/fid/52
----------------------------------------------------------------------
