Multiple foreign keys coming from the same table. It's possible?

Question

Multiple foreign keys coming from the same table. It's possible?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

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

Structure of table_A: id, name, intensity_1, intensity_2

Structure of the table_intensity: int_intensity, description_intensity

My question is: I would like the fields intensity_1 and intensity_2 of table_A to receive the intensity_identity of the intensity table, in a cardinality relation from 1 to 1. That is, create two foreign keys of the same table. It is possible? Is this the right way?

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

Imagine a scenario in the field of oceanography, where table_A, in addition to the primary key, also needs to keep the direction of the wind (wind direction) and the direction of the wave (dir_condulation). Knowing that the table_ventos (id_vento, desc_vento) has all the possible directions, and both the fields dir_vento and dir_ondulacao are also winds, would it be possible for them to hold the id_vento, in the future I will rescue the dsc_vento from the table wind from them ??

Have you more or less tried the idea?

mysql mysql-workbench

asked by anonymous 15.04.2015 / 20:39

3 answers

2

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

Tables may even have foreign keys for themselves.

If it's the right way for your case, you need to know more about the case: Are they always only 2? Is not it worth making one cardinality 1 for many? Or would this complicate the business code too much?

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

15.04.2015 / 20:49

0

It would not be the right way.

In Table_A have only one field called int_identifier .

And every insertion would be a new record with the foreign key. Much easier to control via programming later.

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

15.04.2015 / 20:46

Problem with condition preg_match ('/ ^ \ d * $ /', $ nr_procedure)? 'f': 't'; - what are you doing ? php

score 1 · Accepted Answer

Just run the script and see if that's the case.

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;