CODEWITHSUNDEEP
mysql
terjanq
terjanq

Reputation: 311

Mysql create one-to-one relation

I have 2 tables

  CREATE TABLE `persons` (
 `id` int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `name`
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
CREATE TABLE `person_rank` (
 `id` int(6) NOT NULL,
 `wins` int DEFAULT 0,
 `losts` int DEFAULT 0,
 `rank` int DEFAULT 1000
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How should I create these tables if I want: when one person id added to persons then the same person will be add automatically into person_rank with rank=1000 wins=0 and losts=0?

Upvotes: 0

Views: 52

Answers (2)

Devon Bessemer
Devon Bessemer

Reputation: 35367

I would do as MarcB said and just run two inserts.

Along with what MarcB said, create a foreign key from persons_rank.id to persons.id that cascades on delete and on update. This will keep them in sync. If you change the id on persons, it will update persons_rank. If you delete the row in persons, it will delete the row in persons_rank.

Lastly, you will want id as primary key in persons_rank as well, since it is 1 to 1.

Upvotes: 0

Unlink
Unlink

Reputation: 1003

Create AFTER INSERT trigger on persons

DELIMITER ;;
CREATE TRIGGER `persons_ai` AFTER INSERT ON `persons` FOR EACH ROW
    INSERT INTO `person_rank` (`id`, `wins`, `losts`, `rank`) VALUES (new.id, 0, 0, 1000);;
DELIMITER ;

Upvotes: 1

Related Questions