Create 1: 1 relation in Query Browser

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

Rememberthattheengineofthetableshouldsupportrelationships.(Thatmeans:itneedstobeInnoDBinsteadofMyISAM.)

Inthisexample,each"subscriber" has a "configuration", so we define a foreign key in the configuracoes table, using the id_assinante field:

• In the "Foreign Key" tab, click the "+" in green color
• Set a name for the key (Key Name)
• Tell which table to relate to (Refer Table)
• In the "Column / Foreign Column" list, 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 (which happens in settings if the id_internator is modified)

If the relationship is 1: 1 or 1: n depends on the use to be made - in the database the relationship via foreign key is the same.

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

So, since there can not be more than one "configuration" with the same id_assinante , we will guarantee a 1: 1 ratio.

As requested, here is the SQL command for creating 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;