如何实现单表到另一表的多外键关联？

Hey there! Let's figure out why your relationship diagram isn't showing the connections you want, and how to fix it properly. The issue here is usually either that the foreign key constraints aren't actually created in the database, or the diagram just needs a little nudge to display them.

Step 1: Make Sure Foreign Key Constraints Are Actually Created #

Dragging lines in a diagram tool (like SSMS, Access, etc.) doesn't always mean the constraints are saved to the database. Let's use SQL to explicitly create them—this is the most reliable way.

Assuming your first table is named ConsultationSheet and the second is ItemCatalog, here's how to set it up:

If you're creating the tables from scratch: #

-- First create the ItemCatalog table (it needs a primary key for foreign keys to reference)
CREATE TABLE ItemCatalog (
    ItemID INT PRIMARY KEY, -- This must be a unique identifier (primary key or unique constraint)
    Description VARCHAR(255) NOT NULL
);

-- Now create the ConsultationSheet table with three foreign keys
CREATE TABLE ConsultationSheet (
    -- Add a primary key for your consultation sheet (every table should have one!)
    SheetID INT PRIMARY KEY IDENTITY(1,1),
    Item1a INT,
    Item1b INT,
    Item1c INT,
    -- Create separate foreign key constraints for each field
    CONSTRAINT FK_Consultation_Item1a FOREIGN KEY (Item1a) REFERENCES ItemCatalog(ItemID),
    CONSTRAINT FK_Consultation_Item1b FOREIGN KEY (Item1b) REFERENCES ItemCatalog(ItemID),
    CONSTRAINT FK_Consultation_Item1c FOREIGN KEY (Item1c) REFERENCES ItemCatalog(ItemID)
);

If the tables already exist: #

Use ALTER TABLE to add the constraints one by one:

-- Add constraint for Item1a
ALTER TABLE ConsultationSheet
ADD CONSTRAINT FK_Consultation_Item1a FOREIGN KEY (Item1a) REFERENCES ItemCatalog(ItemID);

-- Add constraint for Item1b
ALTER TABLE ConsultationSheet
ADD CONSTRAINT FK_Consultation_Item1b FOREIGN KEY (Item1b) REFERENCES ItemCatalog(ItemID);

-- Add constraint for Item1c
ALTER TABLE ConsultationSheet
ADD CONSTRAINT FK_Consultation_Item1c FOREIGN KEY (Item1c) REFERENCES ItemCatalog(ItemID);

Step 2: Fix the Relationship Display in the Diagram #

Once the constraints are created in the database, the diagram should show them—but sometimes you need to refresh or manually enable them:

• Refresh the diagram: Right-click the diagram and select "Refresh" to pull the latest schema from the database.
• Show hidden relationships: Right-click a blank area in the diagram, select "Show Relationships", then check the boxes for the three foreign key constraints you created.
• Adjust layout: The three lines might overlap, so drag the tables to spread them out. You can also right-click each line, select "Properties", and verify it's linking the correct field (e.g., Item1a to ItemID).

Key Checks to Avoid Issues #

• Ensure ItemID in ItemCatalog is a primary key or unique constraint—foreign keys can only reference unique fields.
• Match data types exactly: Item1a, Item1b, Item1c must have the same data type as ItemID (e.g., both INT, or both VARCHAR(10)—no mismatches!).
• If your tables already have data: Make sure every value in Item1a, Item1b, Item1c exists in ItemCatalog.ItemID (or set the fields to allow NULL if empty values are allowed). Otherwise, the constraint creation will fail due to existing invalid data.

Once you've done this, your relationship diagram should display three separate lines connecting each of the three fields in your first table to ItemID in the second table, and the foreign key constraints will enforce referential integrity in your database.

内容的提问来源于stack exchange，提问作者D.Trump123