Início / dba / Perguntas / 282799
Accepted
merlin
Asked: 2021-01-08 22:35:02 +0800 CST

Como definir a chave estrangeira em duas tabelas no MySQL?

  • 772

Estou lutando para resolver um projeto de banco de dados para MySQL 5.7 para resolver o seguinte cenário:

Existem dimensões de categoria que mudam a cada dia, eu as armazeno em category_entities. Eles pertencem a uma categoria que se refere a um comerciante e seus produtos:

comerciantes

merchant_id | merchant
1           | amazon
2           | ebay

PK: merchant_id

produtos

prod_id    | merchant_id | product
1          | 1           | jumper big
2          | 1           | hat red
3          | 2           | shoe black
3          | 1           | shoe black2 

PK: merchant_id, prod_id

categorias

cat_id     | merchant_id | category
1          | 1           | jumpers in green 
2          | 1           | hats for woman
3          | 1           | shoes
4          | 2           | jumpers 
5          | 2           | hats for children
6          | 2           | shoes

PK: cat_id

category_entities (30 milhões de linhas)

cat_id | prod_id | characteristica | date
1      | 1       | 23              | 2021-01-07
2      | 2       | 22              | 2021-01-07
1      | 2       | 22              | 2021-01-08
2      | 3       | 01              | 2021-01-08
3      | 1       | 22              | 2021-01-08
4      | 2       | 01              | 2021-01-08

PK: cat_id, prod_id, date

Então a tabela de categorias define quais categorias existem e o category_entities talbe mostra o valor para cada dia

Como posso definir uma chave estrangeira nas tabelas (categories, category_entities) em produtos de tabela usando prod_id, comerciante_id. Esses campos são a chave primária do produto.

Eu estou lendo sobre associações polimórficas, mas não tenho certeza se é uma e como resolver isso.

mysql foreign-key

1 respostas

  1. Best Answer

    Vou adicionar uma resposta parcial, pois é muita informação para colocar nos comentários. Não estou dizendo que é a melhor solução, mas a menos que você queira remodelar, é uma possibilidade. Dados os fatos que você apresentou, você pode estender category_entities como:

    CREATE TABLE category_entities
( cat_id int not null primary key
, prod_id int not null
, merchant_id int not null
, ...
    constraint ... foreign key (prod_id, merchant_id)
                   references products (prod_id, merchant_id)
, ...
);

    Agora, você pode ter informações inconsistentes de Merchant_id entre categorias e category_entities. Você pode evitar isso adicionando uma restrição exclusiva nas categorias:

    ALTER TABLE categories ADD CONSTRAINT ak1_categories
    UNIQUE (cat_id, merchant_id);

    Agora você pode fazer referência a essa restrição em category_entities como:

    ALTER TABLE category_entities ADD CONSTRAINT fk1_categories
    FOREIGN KEY (cat_id, merchant_id)
    REFERENCES categories (cat_id, merchant_id);

    Isso garantirá consistência entre category e category_entities, mas é um pouco feio, pois ak1_categories é uma chave redutível.

    Ouvi rumores de que existe um DBMS que permite restrições CHECK com subconsultas, mas nunca usei um. Além do MySQL 5.7 não se importa com as restrições de verificação, então isso não é uma opção para você.

    Outra alternativa é adicionar antes dos gatilhos para validação do mercante_id. No entanto, são procedimentais por natureza, portanto, não informam nada sobre a situação atual. Tudo o que você sabe é que nenhum dado inválido passou por eles enquanto eles estavam ativos.

    Como mencionado, esta não é uma resposta completa, mas apresenta algumas ideias que não cabem em um comentário.

    • 4

relate perguntas