In one to one relationship the primary key of the first table is foreign key in the second table, and each row from the first table has exactly one or none correspondence in the second table. For proving this relationship, we will:
• Create two tables
• Set the one to one relationship
• Apply a set of JOINs, to select a set of records
• Delete a record in cascade style
Creating two tables
We will create a table named Managers and a table named Teams, and we suppose that each manager has a team and each team has a single manager, which requires the relationship one to one. The tables were designed in MySQL Workbench 5.2 CE, and they look like below:
Set the one to one relationship
Next, we can create a one to one relationship from table Teams to table Managers – the primary key from Tables (id), will be a foreign key in Managers (this will be a generated column). We did that using the MySQL Workbench 5.2 CE EER Diagram designer, and it looks like below:
As you can see the foreign key is represented by the Teams_id column, which was auto-generated for our relationship. The designer reveals this relationship, like below:
The SQL statements that stand behind these operations (create table and relationship), is listed below:
-- -----------------------------------------------------
-- Table `mydb`.`Teams`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Teams` ;
CREATE TABLE IF NOT EXISTS `mydb`.`Teams` (
`id` INT NOT NULL ,
`team` VARCHAR(45) NULL ,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Managers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Managers` ;
CREATE TABLE IF NOT EXISTS `mydb`.`Managers` (
`id` INT NOT NULL ,
`m_name` VARCHAR(45) NULL ,
`m_surname` VARCHAR(45) NULL ,
`invest` VARCHAR(45) NULL ,
`Teams_id` INT NOT NULL ,
PRIMARY KEY (`id`, `Teams_id`) ,
INDEX `fk_Managers_Teams` (`Teams_id` ASC) ,
CONSTRAINT `fk_Managers_Teams`
FOREIGN KEY (`Teams_id` )
REFERENCES `mydb`.`Teams` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Next, you can populate the tables with a few records like below:
The corresponding SQL is listed below:
-- -----------------------------------------------------
-- Data for table `mydb`.`Teams`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`Teams` (`id`, `team`) VALUES (1, 'Manchester United');
INSERT INTO `mydb`.`Teams` (`id`, `team`) VALUES (2, 'Juventus Torino ');
INSERT INTO `mydb`.`Teams` (`id`, `team`) VALUES (3, 'Real Madrid');
INSERT INTO `mydb`.`Teams` (`id`, `team`) VALUES (4, 'FC Barcelona');
INSERT INTO `mydb`.`Teams` (`id`, `team`) VALUES (5, 'FC Liverpool');
INSERT INTO `mydb`.`Teams` (`id`, `team`) VALUES (6, 'FC Steaua');
COMMIT;
-- -----------------------------------------------------
-- Data for table `mydb`.`Managers`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`Managers` (`id`, `m_name`, `m_surname`, `invest`, `Teams_id`)
VALUES (1, 'Roy', 'Hodgson', '10000$', 5);
INSERT INTO `mydb`.`Managers` (`id`, `m_name`, `m_surname`, `invest`, `Teams_id`)
VALUES (2, 'Marcello', 'Lippi', '35000$', 2);
INSERT INTO `mydb`.`Managers` (`id`, `m_name`, `m_surname`, `invest`, `Teams_id`)
VALUES (3, 'Alex', 'Ferguson', '100000$', 1);
INSERT INTO `mydb`.`Managers` (`id`, `m_name`, `m_surname`, `invest`, `Teams_id`)
VALUES (4, 'Jack', 'Greenwell', '80000$', 4);
INSERT INTO `mydb`.`Managers` (`id`, `m_name`, `m_surname`, `invest`, `Teams_id`)
VALUES (5, 'Jose', 'Mourinho', '200000$', 3);
COMMIT;
Apply a set of JOINs, to select a set of records
SQL INNER JOIN Keyword (INNER JOIN is the same as JOIN)
• The INNER JOIN keyword return rows when there is at least one match in both tables.
Teams table contains a record (id:6) that has no match in Managers table. As you can see that record was omitted!!!
SQL LEFT JOIN Keyword(In some databases LEFT JOIN is called LEFT OUTER JOIN.)
• The LEFT JOIN keyword returns all rows from the left table (Teams), even if there are no matches in the right table (Managers).
As you can see, in the LETF JOIN statement the record id:6 from Teams is listed!
SQL RIGHT JOIN Keyword (In some databases RIGHT JOIN is called RIGHT OUTER JOIN)
• The RIGHT JOIN keyword returns all rows from the right table (Managers), even if there are no matches in the left table (Teams).
Delete a record in cascade style
MySQL allows us to specify the delete style, like this:
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
• CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table. Currently, cascaded foreign key actions do not activate triggers.
• SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported. If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.
• RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.
• NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. InnoDB rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.
Our tables were generated with CASCADE reference option, therefore if we delete the from Teams the record with id:3, then the cascade style should automatically result in deleting the record with id:5:
The SQL DELETE statement for this operation:
DELETE FROM Teams WHERE Teams.id=3
After you run this SQL against the database, the result will look like below:
