Multiple foreign keys from the same table. Is it possible?

Question

I am studying Mysql using Mysql Workbrench to create a diagram of my database and the following situation arose: Imagine that I have the table_A and the table_intensity, where :

Structure of table: id, name, intensity_1, intensity_2

Structure of the table_intensity: id_intensity, description_intensity

My question is: I would like the intensity_1 and intensity_2 fields of the table_A to receive the intensity id_intensity of the table, in a cardinality ratio of 1 to 1. That is, create two foreign keys of the same table. Is that possible? That would be the right way?

I’ll try to create a more 'real' scenario'.

Imagine a scenario in the field of oceanography, where the table_A , in addition to the primary key, also needs to keep the direction of the wind (dir_wind) and the direction of the undulation (dir_ondulacao). Knowing that the table winds (id_wind, desc_wind) has all possible directions, and both fields dir_wind and dir_wave are also winds, is it possible that they saved the id_wind, for future I rescue the dsc_wind from the table wind from them?? ie, would be the fields dir_vento and dir_ondulacao foreign keys of the table?

They kind of wanted the idea?

Answer 1

There is no problem in creating more than one foreign key for the same table. Why? Because relationships between tables have semantics and this may require a different link to the same table

Tables may even have a foreign key for themselves.

If it is the right way for your case, you need to know more about the case: Will it always be only 2? Isn’t it worth making a cardinal 1 for many? Or it would complicate the business code too much?

Finally, there is the possibility to do. But the correctness of the solution will depend on more information

Answer 2

Just run the script and see if that’s it.

CREATE TABLE `test`.`tabela_intensidade` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `descricao_intensidade` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

  CREATE TABLE `test`.`tabela_a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `intensidade1` INT NULL,
  `intensidade2` INT NULL,
  PRIMARY KEY (`id`));

  ALTER TABLE `test`.`tabela_a` 
ADD CONSTRAINT `fk01`
  FOREIGN KEY (`id`)
  REFERENCES `test`.`tabela_intensidade` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk02`
  FOREIGN KEY (`id`)
  REFERENCES `test`.`tabela_intensidade` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

INSERT INTO `test`.`tabela_intensidade` (`id`, `descricao_intensidade`) VALUES ('1', 'dir_vento - sul');
INSERT INTO `test`.`tabela_intensidade` (`id`, `descricao_intensidade`) VALUES ('2', 'dir_ondulacao - norte');
INSERT INTO `test`.`tabela_intensidade` (`descricao_intensidade`) VALUES ('dir_vento - leste');
INSERT INTO `test`.`tabela_intensidade` (`descricao_intensidade`) VALUES ('dir_ondulaco - oeste');

INSERT INTO `test`.`tabela_a` (`nome`, `intensidade1`, `intensidade2`) VALUES ('registro01', '1', '2');
INSERT INTO `test`.`tabela_a` (`nome`, `intensidade1`, `intensidade2`) VALUES ('registro02', '3', '4');

SELECT 
a.*,
(select descricao_intensidade from tabela_intensidade b where a.intensidade1=b.id) as intensidade,
(select descricao_intensidade from tabela_intensidade b where a.intensidade2=b.id) as intensidade
FROM test.tabela_a a;

Answer 3

Wouldn’t be the right way.

In Table_a have only one field called id_intensity.

And at each insertion would be a new record with the foreign key. Much easier to control via programming afterwards.

And that would be a relationship with cardinality 1 to 1 or 1 to many.