如何使Trail_info_table的外键Trail_owner_ID关联UserID而非显示为NULL？

问题原因 #

你插入Trail_info_table数据时，未给Trail_owner_ID字段赋值，同时冗余存储了Trail_owner用户名，导致外键字段一直为NULL。

解决方案 #

1. 插入数据时直接关联UserID（推荐）

使用INSERT ... SELECT语法，从User_info_table中根据用户名匹配对应的UserID，直接插入外键值：

-- 插入Spring Sprint记录
INSERT INTO CW2.Trail_info_table (Trail_name, Trail_owner_ID, Trail_difficulty, Trail_length)
SELECT 'Spring Sprint', UserID, 1, 10
FROM CW2.User_info_table
WHERE User_name = 'Grace Hopper';

-- 插入Summer Stroll记录
INSERT INTO CW2.Trail_info_table (Trail_name, Trail_owner_ID, Trail_difficulty, Trail_length)
SELECT 'Summer Stroll', UserID, 2, 15
FROM CW2.User_info_table
WHERE User_name = 'Tim Berners-Lee';

-- 插入Winter Waltz记录
INSERT INTO CW2.Trail_info_table (Trail_name, Trail_owner_ID, Trail_difficulty, Trail_length)
SELECT 'Winter Waltz', UserID, 3, 20
FROM CW2.User_info_table
WHERE User_name = 'Ada Lovelace';

2. 更新已存在的NULL记录

如果已经插入了Trail_owner但Trail_owner_ID为NULL的记录，通过关联表更新外键值：

UPDATE t
SET t.Trail_owner_ID = u.UserID
FROM CW2.Trail_info_table t
JOIN CW2.User_info_table u ON t.Trail_owner = u.User_name;

优化建议 #

• 移除冗余字段：删除Trail_owner字段，后续通过JOIN查询获取用户名，避免数据不一致：

SELECT 
    t.TrailID, 
    t.Trail_name, 
    u.User_name AS Trail_owner, 
    t.Trail_difficulty, 
    t.Trail_length
FROM CW2.Trail_info_table t
JOIN CW2.User_info_table u ON t.Trail_owner_ID = u.UserID;

• 强制外键非空：修改表结构，确保每条trail都有对应的所有者，避免NULL值：

ALTER TABLE CW2.Trail_info_table
ALTER COLUMN Trail_owner_ID int NOT NULL;

内容的提问来源于stack exchange，提问作者Morgan Trainor