ONE-TO-ONE relationship

One person has one address. See the implementation below

Table Description

mysql> SELECT * FROM ADDRESS;
+-----------+--------------+
| PERSON_ID | ADDRESS_TEXT |
+-----------+--------------+
| 1 | Chicago, US |
+-----------+--------------+
1 row in set (0.00 sec)

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.01 sec)

MYSQL Table Structures

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=1 ;

CREATE TABLE IF NOT EXISTS `ADDRESS` (
`PERSON_ID` int(11) NOT NULL,
`ADDRESS_TEXT` varchar(100) NOT NULL,
UNIQUE KEY `PERSON_ID` (`PERSON_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

Insertion queries

mysql> INSERT INTO PERSON(NAME) VALUES('TIFFANY');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO ADDRESS VALUES (1, 'Chicago, US');
Query OK, 1 row affected (0.04 sec)

Table Content

mysql> select * from PERSON;
+----+---------+
| ID | NAME    |
+----+---------+
|  1 | TIFFANY |
+----+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ADDRESS;
+-----------+--------------+
| PERSON_ID | ADDRESS_TEXT |
+-----------+--------------+
|         1 | Chicago, US  |
+-----------+--------------+
1 row in set (0.00 sec)

Deletion of Cascaded Records

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

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

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

Advertisements

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