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

Asked

Viewed 16,929 times

1

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?

  • Wouldn’t it be better to describe a little where you’re going with this?

  • 1

    there’s nothing wrong with the way you’re doing, just because it’s a different case people think you’re wrong, I’ve posted the answer just run the script there.

3 answers

3

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

  • Hello Felipe, I edited my question in an attempt to elaborate it better. Could you take a look and see if you understand where I’m going? Thanks.

  • @sThiago I didn’t understand. You asked if it was possible, I answered it was. As for the "right" way, this is very variable and broad. I would say that this is A correct way. What you expected reply?

  • Your answer helped a lot. Thank you

3


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;
  • Thanks Maison. I guess I’m going the right way. I ended up taking my reasoning this way in an attempt to 'save' tables, since dir_vento is wind and dir_ondulacao is also wind, and as I need to keep each in a table row, but with different values, I thought to create a second table (table_intensity) to store the directions, and use it later in a relationship would be the right one. Thank you again.

  • Maison, last question, last question. How would be a query to show all the records of the table_A, bringing the field descrica_intensity of the table_intensity, related to dir_vento and dir_ondulacao?

  • @sThiago that last question is already there in the script, is SELECT with sub-query relating the FK keys of the table_A with the PK of the table_intensity

  • Exactly that brother. Valew !

1

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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.