如何在单列中存储多个外键值？多表场景输入输出方案咨询

Hey there! Let's walk through your question step by step, since you're looking for a solution that works both for storing multiple foreign keys in a single column and displaying them as needed.

First off: A critical note on database design #

Storing multiple foreign keys in a single column is anti-pattern (anti-normalization). It creates avoidable issues like:

• Data integrity risks (you can't enforce foreign key constraints on a concatenated string)
• Clunky updates (you'd have to split, modify, then re-concatenate the string to change one value)
• Slower, messier queries (filtering or sorting by individual keys becomes a hassle)

If your business logic allows, the cleanest approach is to keep Toolsfk and CarFK as separate columns, then use CONCAT only for output. But if you absolutely need to store them in one column, here's how to handle both input and output:

1. Input: Storing concatenated foreign keys #

You can absolutely use INSERT operations to store the combined keys, but you'll need to concatenate them before inserting. Use a clear delimiter (like a comma) to separate the values—this makes splitting them later much easier.

Example: Insert static values #

INSERT INTO your_target_table (RateID, Costing, CombinedFK)
VALUES (1, 1000, CONCAT(1004, ',', 2003));

Example: Insert by joining your 4 tables #

INSERT INTO your_target_table (RateID, Costing, CombinedFK)
SELECT 
  r.RateID, 
  c.Costing, 
  CONCAT(t.Toolsfk, ',', ca.CarFK) AS CombinedFK
FROM rate r
JOIN cost c ON r.RateID = c.RateID
JOIN tools t ON r.RateID = t.RateID
JOIN car ca ON r.RateID = ca.RateID;

⚠️ Important: Make sure your foreign key values don't contain the delimiter you choose—otherwise, splitting the string later will break.

2. Output: Displaying the combined keys as desired #

When you need to show the values in your target format (without the delimiter), you can either use CONCAT again (if pulling from separate columns) or replace the delimiter if stored as a single string:

If stored as a single column: #

SELECT 
  RateID, 
  Costing, 
  REPLACE(CombinedFK, ',', ' ') AS `Toolsfk and CarFK`
FROM your_target_table;

If you need to split the values later (for individual use): #

Different databases have different string-splitting functions:

• MySQL: Use SUBSTRING_INDEX(CombinedFK, ',', 1) to get the first key, SUBSTRING_INDEX(CombinedFK, ',', -1) for the second
• PostgreSQL: SPLIT_PART(CombinedFK, ',', 1)
• SQL Server: STRING_SPLIT(CombinedFK, ',')

3. Is using only INSERT enough? #

Not exactly. While INSERT handles the initial storage:

• If you need to update the combined key later (e.g., change Toolsfk), you'll have to split the existing string, modify the relevant value, re-concatenate, then run an UPDATE query—this is way more work than updating a separate column.
• Queries that need to filter or sort by individual keys will be slower and more complex compared to using separate columns.

The better alternative: Stick to normalized design #

If possible, always keep Toolsfk and CarFK as separate columns. This is the industry standard for good reason:

-- Insert with separate columns
INSERT INTO your_target_table (RateID, Costing, Toolsfk, CarFK)
VALUES (1, 1000, 1004, 2003);

-- Output with concatenation
SELECT 
  RateID, 
  Costing, 
  CONCAT(Toolsfk, ' ', CarFK) AS `Toolsfk and CarFK`
FROM your_target_table;

This approach maintains data integrity (you can add foreign key constraints), makes updates/queries faster, and avoids all the headaches of string manipulation.

内容的提问来源于stack exchange，提问作者Ainal Yaqin