ONE-TO-MANY / MANY-TO-ONE relationship

It is quiet obvious,

one person can have more than one order

one order can have more than one product

one product can have multiple items

I hope this is easy to imagine.

Table Description

mysql> DESC PERSON;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| ID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME  | varchar(15) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESC ORDERS;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| ID        | int(11)     | NO   | PRI | NULL    | auto_increment |
| PERSON_ID | int(11)     | NO   | MUL | NULL    |                |
| NAME      | varchar(15) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

DDL

CREATE TABLE IF NOT EXISTS `PERSON` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(15) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT ;

CREATE TABLE IF NOT EXISTS `ORDERS` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`PERSON_ID` int(11) NOT NULL,
`NAME` varchar(15) NOT NULL,
PRIMARY KEY (`ID`),
KEY `PERSON_ID` (`PERSON_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT ;

ALTER TABLE `ORDERS`
ADD CONSTRAINT `ORDERS_ibfk_1` FOREIGN KEY (`PERSON_ID`) REFERENCES `PERSON` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

DML

INSERT INTO `PERSON` (`ID`, `NAME`) VALUES (2, 'TIFFANY');
INSERT INTO `ORDERS` (`ID`, `PERSON_ID`, `NAME`) VALUES
(1, 2, 'GROCERY'),
(2, 2, 'COSMETICS');

Table Content

mysql> SELECT * FROM PERSON;
+----+---------+
| ID | NAME    |
+----+---------+
|  2 | TIFFANY |
+----+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ORDERS;
+----+-----------+-----------+
| ID | PERSON_ID | NAME      |
+----+-----------+-----------+
|  1 |         2 | GROCERY   |
|  2 |         2 | COSMETICS |
+----+-----------+-----------+
2 rows in set (0.00 sec)

Delation of Cascaded Records

mysql> DELETE FROM PERSON WHERE ID=2;
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM ORDERS;
Empty set (0.00 sec)

mysql> SELECT * FROM PERSON;
Empty set (0.00 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s