secundaria - Posible hacer una clave externa de MySQL a una de dos tablas posibles?

llave foranea sql server

Question

Bueno, aquí está mi problema, tengo tres tablas; regiones, países, estados. Los países pueden estar dentro de las regiones, los estados pueden estar dentro de las regiones. Las regiones son la parte superior de la cadena alimentaria.

Ahora agrego una tabla popular_areas con dos columnas; region_id y popular_place_id. ¿Es posible hacer que popular_place_id sea una clave externa para cualquiera de los países o estados? Probablemente tenga que agregar una columna popular_place_type para determinar si la identificación está describiendo un país o estado de cualquier manera.

Answer 1

Aquí hay una corrección al enfoque "supertable" de Bill Karwin, usando una clave compuesta ( place_type, place_id ) para resolver las violaciones percibidas de forma normal:

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = ''state'', ''country'' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT ''state'' NOT NULL
     CHECK ( place_type = ''state'' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT ''country'' NOT NULL
     CHECK ( place_type = ''country'' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  UNIQUE ( user_id, place_id ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
);

Lo que este diseño no puede garantizar para cada fila en places existe una fila en states o countries (pero no ambos). Esta es una limitación de claves externas en SQL. En un DBMS completo conforme a los estándares SQL-92, podría definir restricciones entre tablas diferibles que le permitirían lograr lo mismo pero es torpe, implica transacciones y ese DBMS aún no ha llegado al mercado.

Answer 2

Esta no es la solución más elegante del mundo, pero podría usar una herencia concreta de tablas para que esto funcione.

Conceptualmente, usted está proponiendo una noción de una clase de "cosas que pueden ser áreas populares" de las cuales heredan sus tres tipos de lugares. Puede representar esto como una tabla llamada, por ejemplo, places donde cada fila tiene una relación de uno a uno con una fila en regions , countries o states . (Los atributos que se comparten entre regiones, países o estados, si los hay, se pueden insertar en esta tabla de lugares). Su popular_place_id sería una referencia de clave externa a una fila en la tabla de lugares que luego lo conduciría a una región, país o estado.

La solución que propone con una segunda columna para describir el tipo de asociación pasa a ser cómo Rails maneja las asociaciones polimórficas, pero no soy un fanático de eso en general. Bill explica con excelente detalle por qué las asociaciones polimórficas no son tus amigos.

Answer 3

Lo que describes se llama Asociaciones polimórficas. Es decir, la columna "clave externa" contiene un valor de ID que debe existir en uno de un conjunto de tablas de destino. Normalmente, las tablas de destino están relacionadas de alguna manera, como ser instancias de alguna superclase común de datos. También necesitaría otra columna junto a la columna de clave externa, para que en cada fila pueda designar a qué tabla de destino se hace referencia.

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either ''states'' or ''countries''
  -- foreign key is not possible
);

No hay forma de modelar las Asociaciones polimórficas utilizando restricciones SQL. Una restricción de clave externa siempre hace referencia a una tabla de destino.

Las asociaciones polimórficas son compatibles con marcos tales como Rails e Hibernate. Pero explícitamente dicen que debe desactivar las restricciones SQL para usar esta característica. En cambio, la aplicación o el marco debe hacer un trabajo equivalente para garantizar que se satisfaga la referencia. Es decir, el valor en la clave externa está presente en una de las posibles tablas de objetivos.

Las asociaciones polimórficas son débiles con respecto a hacer cumplir la coherencia de la base de datos. La integridad de los datos depende de que todos los clientes accedan a la base de datos con la misma lógica de integridad referencial impuesta, y también la aplicación debe estar libre de errores.

Aquí hay algunas soluciones alternativas que aprovechan la integridad referencial impuesta por la base de datos:

Crea una tabla extra por objetivo. Por ejemplo, popular_states y popular_countries , que hacen referencia a states y countries respectivamente. Cada una de estas tablas "populares" también hace referencia al perfil del usuario.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

Esto significa que para obtener todos los lugares favoritos populares de un usuario, necesita consultar ambas tablas. Pero significa que puede confiar en la base de datos para hacer cumplir la coherencia.

Crea una tabla de places como una supertabla. Como menciona Abie, una segunda alternativa es que sus lugares populares hagan referencia a una tabla como places , que es un padre de states y countries . Es decir, tanto los estados como los países también tienen una clave externa para los places (incluso puede hacer que esta clave foránea sea también la clave principal de states y countries ).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Usa dos columnas En lugar de una columna que puede hacer referencia a cualquiera de las dos tablas de objetivos, use dos columnas. Estas dos columnas pueden ser NULL ; de hecho, solo uno de ellos debe ser no NULL .

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

En términos de teoría relacional, las Asociaciones polimórficas violan la Primera forma normal , porque popular_place_id es en realidad una columna con dos significados: es un estado o un país. No almacenaría la age una persona ni su número de phone_number en una sola columna, y por la misma razón no debería almacenar tanto state_id como country_id en una sola columna. El hecho de que estos dos atributos tengan tipos de datos compatibles es una coincidencia; todavía significan diferentes entidades lógicas.

Las Asociaciones polimórficas también violan la Tercera Forma Normal , porque el significado de la columna depende de la columna adicional que nombra la tabla a la que se refiere la clave externa. En la Tercera Forma Normal, un atributo en una tabla debe depender solo de la clave primaria de esa tabla.

Comentario de @SavasVedova:

No estoy seguro de seguir su descripción sin ver las definiciones de tabla o una consulta de ejemplo, pero parece que simplemente tiene varias tablas de Filters , cada una contiene una clave externa que hace referencia a una tabla central de Products .

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

Unir los productos a un tipo específico de filtro es fácil si sabes a qué tipo te quieres unir:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

Si desea que el tipo de filtro sea dinámico, debe escribir el código de la aplicación para construir la consulta SQL. SQL requiere que la tabla se especifique y se corrija en el momento de escribir la consulta. No puede hacer que la tabla unida se elija de forma dinámica en función de los valores encontrados en filas individuales de Products .

La única otra opción es unirse a todas las tablas de filtro utilizando combinaciones externas. Aquellos que no tengan product_id correspondiente se devolverán como una sola fila de nulos. Pero aún tiene que codificar todas las tablas unidas, y si agrega nuevas tablas de filtro, debe actualizar su código.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

Otra forma de unirse a todas las tablas de filtro es hacerlo en serie:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

Pero este formato todavía requiere que escriba referencias a todas las tablas. No hay forma de evitar eso.