Create 1:1 relation in Query Browser

Question

I would like to know how to create 1:1 relations in Query Browser.

I don’t know if I’m right, but he creates 1:n relationships by creating Foreign Keys.

Answer 1

In the image below you can see an example of relation 1:1 in the Query Browser:

Remember that the engine table must support relationships. (Meaning: needs to be Innodb instead of Myisam.)

In this example, each "subscriber" has a "configuration", so we define a foreign key (Foreign key) on the table configuracoes, using the field id_assinante:

• In the "Foreign Key" tab, click on the green "+"
• Set a name for the key (Key Name)
• Tell which table is related to (Refer. Table)
• In the "Column / Foreign Column" listing, you can drag the "Column" of the current table, and in the corresponding "Foreign Column", put the field name of the other table (in the example, both have the same name: id_assinante)

• You can still determine what happens in this table if any changes happen in the other table:

  • "On Delete" determines what happens here if the related column is deleted in the other table (what happens in settings if the subscriber is deleted)
  • "On Update" determines what happens here if the related column is changed in the other table (what happens in settings if the id_subscriber is modified)

---

Whether the ratio is 1:1 or 1:n depends on the use that will be made - in the database the link via Foreign key is the same.

To force 1:1, go to the "Index" tab and include an index of type UNIQUE to the country id_assinante in the same table where the foreign key was defined.

Thus, there can be no more than one "configuration" with the same id_assinante, we will ensure a 1:1 relationship.

---

As requested, here is the SQL command to create the table:

CREATE TABLE  `pdm`.`configuracoes` (
  `id_configuracao` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_assinante` int(10) unsigned NOT NULL,
  `assinatura_ativa` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_configuracao`),
  UNIQUE KEY `id_assinante_UNIQUE` (`id_assinante`),
  CONSTRAINT `fk_configuracoes_assinantes` FOREIGN KEY (`id_assinante`)
    REFERENCES `assinantes` (`id_assinante`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;