如何在多对多（M:N）数据库系统中识别未使用的值？

Alright, let's break down how to spot unused entries in this components-suppliers-widgets assembly system. First, I'll fill in the logical missing pieces (your widgets table definition got cut off, and we need junction tables for the M:N relationships—critical for linking components to suppliers (with cost) and components to assemblies).

Assuming your full schema includes these key junction tables:

-- Links components to suppliers with their associated cost
CREATE TABLE component_supplier (
  c_id INT NOT NULL,
  s_id INT NOT NULL,
  cost DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (c_id, s_id),
  FOREIGN KEY (c_id) REFERENCES components(c_id),
  FOREIGN KEY (s_id) REFERENCES suppliers(s_id)
);

-- Links widgets (assemblies) to the components they use
CREATE TABLE widget_component (
  w_id INT NOT NULL,
  c_id INT NOT NULL,
  PRIMARY KEY (w_id, c_id),
  FOREIGN KEY (w_id) REFERENCES widgets(w_id),
  FOREIGN KEY (c_id) REFERENCES components(c_id)
);

Now let's tackle each type of unused value:

1. Unused Components (Never used in any assembly) #

These are components that exist in your components table but haven't been added to any widget/assembly:

SELECT c.c_id, c.name_c
FROM components c
LEFT JOIN widget_component wc ON c.c_id = wc.c_id
WHERE wc.w_id IS NULL;

How it works: A left join keeps all components, even those without a matching assembly link. Filtering for wc.w_id IS NULL gives us the components with no assembly associations.

2. Unused Suppliers (Never supply any component) #

Suppliers that are in your suppliers table but have no recorded component-cost relationships:

SELECT s.s_id, s.name_s
FROM suppliers s
LEFT JOIN component_supplier cs ON s.s_id = cs.s_id
WHERE cs.c_id IS NULL;

How it works: Same left join logic—we keep all suppliers, then filter out those with no component links.

3. Unused Component-Supplier Cost Combinations #

These are valid supplier-cost entries for a component, but either the component is never used, or this specific supplier's offering for the component is never chosen for an assembly.

3.1 Combos where the component itself is unused #

SELECT cs.c_id, cs.s_id, cs.cost, c.name_c, s.name_s
FROM component_supplier cs
JOIN components c ON cs.c_id = c.c_id
JOIN suppliers s ON cs.s_id = s.s_id
LEFT JOIN widget_component wc ON cs.c_id = wc.c_id
WHERE wc.w_id IS NULL;

3.2 Combos where the component is used, but this supplier isn't selected #

Note: This assumes your system tracks which supplier's parts are used in each assembly (if so, you'd have a widget_component_supplier table linking assemblies directly to component-supplier pairs).

-- First, the junction table for assembly-specific supplier selection
CREATE TABLE widget_component_supplier (
  w_id INT NOT NULL,
  c_id INT NOT NULL,
  s_id INT NOT NULL,
  PRIMARY KEY (w_id, c_id, s_id),
  FOREIGN KEY (w_id) REFERENCES widgets(w_id),
  FOREIGN KEY (c_id, s_id) REFERENCES component_supplier(c_id, s_id)
);

-- Query for unused component-supplier combos
SELECT cs.c_id, cs.s_id, cs.cost, c.name_c, s.name_s
FROM component_supplier cs
JOIN components c ON cs.c_id = c.c_id
JOIN suppliers s ON cs.s_id = s.s_id
LEFT JOIN widget_component_supplier wcs 
  ON cs.c_id = wcs.c_id AND cs.s_id = wcs.s_id
WHERE wcs.w_id IS NULL;

4. Empty Assemblies (Widgets with no components) #

If you need to find assemblies that don't use any components at all:

SELECT w.w_id, w.name_w
FROM widgets w
LEFT JOIN widget_component wc ON w.w_id = wc.w_id
WHERE wc.c_id IS NULL;

Quick Tips #

• Always double-check your foreign key constraints—they prevent orphaned data that could skew these results.
• If your actual schema differs slightly (e.g., different junction table names), just adjust the JOIN conditions to match your setup. The core logic of left joins + filtering for NULL matches is universal for finding unused entries in relational databases.

内容的提问来源于stack exchange，提问作者John Jackson